BookmarkSubscribeRSS Feed
twinkle_k12
Calcite | Level 5

Hello SAS users,

 

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

 

This is my input 

IdParticipationgrade
54Notactivea
54activeb
55Notactivec
55actived

 

And the required output format

IdNotactive_indNotactive_gradeActive_indActive_grade
54Non-activeaactiveb
55Non-activecactived

 

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

2 REPLIES 2
Tom
Super User Tom
Super User

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
Ksharp
Super User
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 529 views
  • 1 like
  • 3 in conversation