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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.