topic Re: Macro for transpose in SAS Programming

Macro for transpose

stancemcgraw — Thu, 20 Sep 2018 21:29:21 GMT

data have:

StudyNum	Response	ScoreAssessment	Assessment	AssessmentDT
1233	Y		Frailty	7/8/18 6:00
1233	N		Dementia
1096		2	Frailty
1236	N		Dementia
1236	Y	3	Frailty	7/11/18 3:00
1253	Y	4	Frailty	8/9/18 5:00

data want:

each study number has a response, a score assessment, an assessment, and an assessment DT long ways. Is there a macro code to transpose those variables so I don't have to do several transpose steps?

Re: Macro for transpose

novinosrin — Thu, 20 Sep 2018 21:31:11 GMT

data want is not clear to me, may i request you to provide that too

Re: Macro for transpose

stancemcgraw — Thu, 20 Sep 2018 21:35:45 GMT

StudyNum	Response	ScoreAssessment	Assessment	AssessmentDT
1233	Y		Frailty	7/8/18 6:00
1233	N		Dementia
1096		2	Frailty
1236	N		Dementia
1236	Y	3	Frailty	7/11/18 3:00
1253	Y	4	Frailty	8/9/18 5:00

data want:

studynum FrailtyScore Dementia Score Frailty Dementia FrailtyAssessmentDT Dementia AssessmentDT

1233 3 . Yes No 7/8/16: 6:00 .

Re: Macro for transpose

data_null__ — Thu, 20 Sep 2018 22:03:38 GMT

data have;
   infile cards missover;
   input StudyNum 4. + 1 Response $1. +1 ScoreAssessment 1.  Assessment:$16. AssessmentDT &mdyampm.;
   format AssessmentDT  datetime.; 
   cards;
1233 Y   Frailty 7/8/18 6:00 
1233 N   Dementia   
1096   2 Frailty   
1236 N   Dementia   
1236 Y 3 Frailty 7/11/18 3:00 
1253 Y 4 Frailty 8/9/18 5:00 
;;;;
   run;
proc sort;
   by studynum;
   run;
proc print;
   run; 
%macro t(var=);
   proc transpose data=have out=&var(drop=_name_) suffix=&var;
      by studynum;
      var &var;
      id Assessment;
      run;
   %mend t;
%t(var=ScoreAssessment);
%t(var=Response)
%t(var=AssessmentDT);

data want;
   merge ScoreAssessment response Assessmentdt;
   by studynum;
   run;

Re: Macro for transpose

ballardw — Thu, 20 Sep 2018 22:28:34 GMT

@stancemcgraw wrote:

StudyNum Response ScoreAssessment Assessment AssessmentDT

1233 Y Frailty 7/8/18 6:00

1233 N Dementia

1096 2 Frailty

1236 N Dementia

1236 Y 3 Frailty 7/11/18 3:00

1253 Y 4 Frailty 8/9/18 5:00

data want:

studynum FrailtyScore Dementia Score Frailty Dementia FrailtyAssessmentDT Dementia AssessmentDT

1233 3 . Yes No 7/8/16: 6:00 .

Please make output data based on example input. Your input does not show any value for FrailtyScore (or any value of 3 for that matter) associated with studynum=1233

Re: Macro for transpose

stancemcgraw — Fri, 21 Sep 2018 16:02:48 GMT

Great thank you that worked!!!!