Hi,
I need help to produce the WANT dataset from my HAVE dataset.
data want;
input num code $;
cards;
1 A
1 B
2 C
2 D
;
run;
data HAVE;
input num code $;
cards;
1 A,B
2 C,D
;
run;
With a data step and by group processing:
data have;
input num code $;
cards;
1 A
1 B
2 C
2 D
;
run;
data want;
set have (rename=(code=_code));
by num;
retain code " "; * make long enough to hold all values;
if first.num then code = '';
code = catx(',',code,_code);
if last.num then output;
drop _code;
run;
proc print data=want noobs;
run;
Result:
num code 1 A,B 2 C,D
Correct order of dataset have by num is required.
Another possible solution:
data have;
input num code $;
cards;
1 A,B
2 C,D
;
run;
Data Want;
Set have;
Col_1 = Scan(code, 1, ",");
Col_2 = Scan(code, 2, ",");
Drop code;
Run;
Proc Transpose Data = Want
Out = Want (Rename=(Col1=code) Drop=_NAME_);
By num;
Var Col_1 Col_2;
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.