Hello,
I have a raw datasets and I need to create 3 different dataset per following criteria.
Output all covid observations to Covid dataset
If covid = 1 (yes) then output result observation in Result dataset
If covid = 1 (yes) and result = 1 (yes) then output result observation in Treatment dataset
PROC FORMAT;
VALUE TRT
0 = "NO"
1 = "YES"
;
RUN;
DATA HAVE;
INPUT var1 $5. var2 $ var3;
FORMAT VAR3 TRT.;
datalines;
CASE1 COVID 0
CASE1 RESULT .
CASE1 TRETMENT .
CASE2 COVID 1
CASE2 RESULT 0
CASE2 TRETMENT .
CASE3 COVID 1
CASE3 RESULT 1
CASE3 TRETMENT .
CASE4 COVID 1
CASE4 RESULT 1
CASE4 TRETMENT 1
CASE5 COVID 1
CASE5 RESULT 1
CASE5 TRETMENT 0
CASE6 COVID 1
CASE6 RESULT 0
CASE6 TRETMENT 0
;
run;
PROC SORT DATA=HAVE;
BY VAR1;
RUN;
DATA COVID RESULT TREATMENT;
SET HAVE;
BY VAR1;
IF VAR2 = "COVID" THEN OUTPUT COVID;
/* Not working */
IF FIRST.VAR1 THEN DO;
IF VAR3 = 1 THEN DO;
IF VAR2 = "RESULT" THEN OUTPUT RESULT;
END;
END;
RUN;
Try something like this:
DATA COVID RESULT TREATMENT;
retain covid_flag 0;
drop covid_flag;
SET HAVE;
BY VAR1;
if first.var1 then do;
IF VAR2 = "COVID" THEN do;
OUTPUT COVID;
covid_flag=1;
end;
end;
if var2="RESULT" and var3 then output RESULT;
if var2="TRETMENT" and covid_flag and var3 then output TREATMENT;
if last.var1 then covid_flag=0;
RUN;
It did not work for me
Wouldn't it be easier to transpose first?
24109 proc transpose data=have out=wide ; 24110 by var1; 24111 id var2; 24112 var var3; 24113 run; NOTE: There were 18 observations read from the data set WORK.HAVE. NOTE: The data set WORK.WIDE has 6 observations and 5 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 24114 24115 data covid result treatment; 24116 set wide; 24117 if covid then do; 24118 output covid result; 24119 if result then output treatment; 24120 end; 24121 run; NOTE: There were 6 observations read from the data set WORK.WIDE. NOTE: The data set WORK.COVID has 5 observations and 5 variables. NOTE: The data set WORK.RESULT has 5 observations and 5 variables. NOTE: The data set WORK.TREATMENT has 3 observations and 5 variables
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.