Hi ,
How to transpose below data set to the desired data set.
actual data set:
NO | DATE_EFFCT | CLASS | PLAN | KEY |
1 | 20210817 | ARP | 1 | XXX001 |
2 | 20210817 | ARP | 1 | XXX001 |
1 | 20201012 | SSP | 2 | XXX002 |
2 | 20201013 | SSP | 2 | XXX002 |
desired data set:
KEY | DATE_EFFCT | NO_1 | CLASS_1 | PLAN_1 | NO_2 | CLASS_2 | PLAN_2 |
XXX001 | 20210817 | 1 | ARP | 1 | 2 | ARP | 1 |
XXX002 | 20201012 | 1 | SSP | 2 | |||
XXX002 | 20201013 | 2 | SSP | 2 |
and if there are 3 Nos then NO_3 CLASS_3 PLAN_3 should automatically be created.
The group variables should be KEY and DATE_EFFCT.
Request help on this.
Thanks,
Swathi
The easiest way is to use PROC SUMMARY.
data have;
input NO DATE_EFFCT :yymmdd. CLASS $ PLAN KEY $;
format date_effct yymmdd10.;
cards;
1 20210817 ARP 1 XXX001
2 20210817 ARP 1 XXX001
1 20201012 SSP 2 XXX002
2 20201013 SSP 2 XXX002
;
proc summary data=have;
by key date_effct ;
output out=want(drop=_type_ _freq_) idgroup(out[3] (no class plan)=);
run;
If the data isn't pre-sorted then use CLASS statement (but depending on how many groups you have might require too much memory).
proc summary data=have nway;
class key date_effct / missing;
output out=want(drop=_type_ _freq_) idgroup(out[3] (no class plan)=);
run;
But you do need to know what number to put into the square brackets after the OUT keyword in the IDGROUP() block. If you put something too small then the extra sets of vlaues will not be included.
To make it dynamic you could use an SQL query to find the maximum per by group (class).
proc sql noprint;
select max(count) into :ngroups trimmed
from (select key,date_effct,count(*) as count from have group by key,date_effct)
;
quit;
proc summary data=have nway;
class key date_effct / missing ;
output out=want(drop=_type_ _freq_) idgroup(out[&ngroups] (no class plan)=);
run;
Results:
Obs KEY DATE_EFFCT NO_1 NO_2 CLASS_1 CLASS_2 PLAN_1 PLAN_2 1 XXX001 2021-08-17 1 2 ARP ARP 1 1 2 XXX002 2020-10-12 1 . SSP 2 . 3 XXX002 2020-10-13 2 . SSP 2 .
NOTE: I cannot figure out why you have the values for that third observations under the second block of variables instead of the first. Is that just a mistake? If it is on purpose then you need to explain what logic made you decide to put it into the second block instead of the first.
data have;
input NO DATE_EFFCT :yymmdd. CLASS $ PLAN KEY $;
format date_effct yymmdd10.;
cards;
1 20210817 ARP 1 XXX001
2 20210817 ARP 1 XXX001
1 20201012 SSP 2 XXX002
2 20201013 SSP 2 XXX002
;
proc sql noprint;
select distinct catt('have(where=(no_',no,'=',no,') rename=
(no=no_',no,' class=class_',no,' plan=plan_',no,'))') into : merge separated by ' '
from have;
quit;
data want;
if 0 then set have(keep=key date_effct);
merge &merge.;
by key date_effct;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.