Author: kuekawa

Hello, I have a variable that includes multiple values, separated by commas. For example, I have a variable URBANICITY. For each subjects, I have values like this:

Urbanicity
Urban
Urban, Rural
Rural, Suburb
Suburb,
Urban, Rural, Suburb
***********

data old;
input Urbanicity $20.;
cards;
Urban
Urban, Rural
Rural, Suburb
Suburb
Urban, Rural, Suburb
;
run;

data temp;
set old;
numw=countw(urbanicity,',');
do i=1 to numw;
new_urban=scan(compress(urbanicity),i,',');
output;
end;
run;

proc print data=temp;
run;

proc freq data=temp;
tables new_urban;
run;
Thanks SAS!

I have a text file in which there are data entries like this:

apple orange fish meet kite stone

I want to create a SAS dataset like this:

VAR1 <-- this is variable name.
apple
orange
fish

*************

Hi,

To read a record which contains multiple values and create multiple observations with 1 variable, you can use the INPUT statement with the double trailing @. I created a sample record with your data and then used the DATA step with INFILE and INPUT statement to read in the data to create multiple observations with 1 variable. Here is the code:

data one;
infile 'c:\temp\input2.txt';
input v1 $ @@;
run;

proc print;
run;

Here are the results from PROC PRINT:

Obs v1

1 apple
2 orange
3 fish
4 meet
5 kite
6 stone
<Thanks,AE>

Add prescript

%let old=sashelp.shoes;
%let new=newdataname; /*it is okay for the old name and the new name to be the same*/
%let prefix=abcd_;

data XXX;set &old;
run;
PROC SQL NOPRINT;
SELECT TRIM(NAME)||"=&prefix"||TRIM(NAME)
INTO :VARLIST SEPARATED BY ' '
FROM DICTIONARY.COLUMNS
WHERE LIBNAME EQ "WORK" AND MEMNAME EQ "XXX"
ORDER BY VARNUM;
QUIT;
PROC DATASETS LIBRARY=WORK NOLIST;
MODIFY XXX;
RENAME &VARLIST;
QUIT;
data &new;
set XXX;
run;

 

 

Add postscript

PROC SQL NOPRINT;
SELECT TRIM(NAME)||"="||TRIM(NAME)||"&post"
INTO :VARLIST SEPARATED BY ' '
FROM DICTIONARY.COLUMNS
WHERE LIBNAME EQ "WORK" AND MEMNAME EQ "XXX"
ORDER BY VARNUM;
QUIT;

 

If you need some variables not renamed in this process, modify the where statement:

WHERE LIBNAME EQ "WORK" AND MEMNAME EQ "XXX"

and upcase(name)  ne 'GENDER'

and upcase(name)  ne 'ID'

and upcase(name)  ne 'YEAR'