Hi Guys,
I am trying to convert one dataset into another (example given below)
Input Data
Name | Age | String |
---|---|---|
John | 28 | A,BB |
Jack | 25 | M,BM,M4m,44,65 |
Jill | 26 |
Output Data
Name | Age | String_Old | String_New |
---|---|---|---|
John | 28 | A,BB | A |
John | 28 | A,BB | BB |
Jack | 25 | M,BM,M4m,44,65 | M |
Jack | 25 | M,BM,M4m,44,65 | BM |
Jack | 25 | M,BM,M4m,44,65 | M4m |
Jack | 25 | M,BM,M4m,44,65 | 44 |
Jack | 25 | M,BM,M4m,44,65 | 65 |
Jill | 26 |
This is getting too complicated for the SAS skills that i have. Can anyone help solve the same?
Regards,
You could do it with something like:
data want (drop=i);
set have;
i=1;
if scan(string,i) eq "" then output;
else do while (scan(string,i) ne "");
new_string=scan(string,i);
output;
i+1;
end;
run;
You could do it with something like:
data want (drop=i);
set have;
i=1;
if scan(string,i) eq "" then output;
else do while (scan(string,i) ne "");
new_string=scan(string,i);
output;
i+1;
end;
run;
Thanks...the way you did it is so simple...i was trying to complicate it by thinking about counting the commas and then creating variable basis highest number of occurence of comma...phew...thanks again
Simple is usually the correct answer. It's just usually difficult to think of how to simplify!
Basically the same as Art's method:
data foo;
infile cards missover;
input name $ age string $20.;
if missing(string) then output;
else do i=1 to countw(string);
new_string=scan(string,i);
output;
end;
drop i;
cards;
John 28 A,BB
Jack 25 M,BM,M4m,44,65
Jill 26
;
run;
another similarly way:
data have;
infile datalines missover;
informat string $40.;
input name$ age string &;
datalines;
John 28 A,BB
Jack 25 M,BM,M4m,44,65
Jill 26
;
data want (drop=i n rename=(string=string_old));
retain name age string string_new;
length string_new $8;
set have ;
n=countw(string);
if n=0 then output;
else do;
do i=1 to n;
string_new=scan(string,i);
output;
end;
end;
run;
proc print;
run;
Hi ... a variation on the other postings (without the LENGTH statement, NEW_STRING has a length of 200) ...
data want;
length new_string $5.;
set have;
do _n_=1 to ifn(countw(string), countw(string), 1);
new_string=scan(string,_n_);
output;
end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.