topic Re: SAS PROC Transpose - help needed in New SAS User

SAS PROC Transpose - help needed

twinkle_k12 — Fri, 17 Dec 2021 14:12:22 GMT

Hello SAS users,

I am a novice SAS user and need help for one proc transpose step.

This is my input

Id	Participation	grade
54	Notactive	a
54	active	b
55	Notactive	c
55	active	d

And the required output format

Id	Notactive_ind	Notactive_grade	Active_ind	Active_grade
54	Non-active	a	active	b
55	Non-active	c	active	d

Could someone pour any suggestions to achieve this output please. Any help is highly appreciated

Re: SAS PROC Transpose - help needed

Tom — Fri, 17 Dec 2021 15:14:22 GMT

Huh? The proposed structure seems strange as two of the columns are constants.

proc transpose data=have out=want(drop=_name_) suffix=_grade;
  by id;
  id participation;
  var grade ;
run;

If you want to have those two other columns to indicate if there is a grade for that type of participation then perhaps you want to add it afterwards? Might be better to use 0/1 instead of 'active' as the value of that indicator.

data want2;
  set want;
  notactive_ind=not missing(notactive_grade);
  active_ind=not missing(active_grade);
run;

             Notactive_    active_    notactive_    active_
Obs    Id      grade        grade         ind         ind

 1     54        a            b            1           1
 2     55        c            d            1           1
 3     56        c                         1           0
 4     57                     d            0           1

Re: SAS PROC Transpose - help needed

Ksharp — Sat, 18 Dec 2021 14:06:17 GMT

data have;
infile cards expandtabs;
input Id	Participation :$20.	grade $;
cards;
54	Notactive	a
54	active	b
55	Notactive	c
55	active	d
;

proc sql noprint;
select distinct catt('have(where=(',Participation,'_ind="',Participation,'")
  rename=( Participation=',Participation,'_ind grade=',Participation,'_grade))')
into : merge separated by ' '
 from have ;
quit;
data want;
 merge &merge;
 by id;
run;