topic Re: transposing variables in SAS Programming

transposing variables

swathi5 — Wed, 02 Feb 2022 02:37:23 GMT

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

Re: transposing variables

Tom — Wed, 02 Feb 2022 04:04:16 GMT

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.

Re: transposing variables

Ksharp — Wed, 02 Feb 2022 07:04:35 GMT

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;