topic Re: Create dataset based on the condition in SAS Programming

Create dataset based on the condition

dht115 — Fri, 25 Mar 2022 20:26:12 GMT

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;

Re: Create dataset based on the condition

SASJedi — Sun, 27 Mar 2022 14:50:23 GMT

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;

Re: Create dataset based on the condition

Tom — Sun, 27 Mar 2022 15:52:21 GMT

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

Re: Create dataset based on the condition

dht115 — Sun, 27 Mar 2022 17:33:52 GMT

It did not work for me