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?
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;
data want is not clear to me, may i request you to provide that too
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 .
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;
Great thank you that worked!!!!
@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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.