SQL or any appropriate method to select elements under columns and common exposures

ak2011 — Sat, 10 Oct 2020 03:47:35 GMT

Hello, I would appreciate if someone helps me an appropriate approach to complete this task using 
dataset d1 having lung cancer cases cacase and controls popcont exposed to 4 agents a1,a2,a3 and a4.
I need to
(1)Place exposures(1's) of each agent under its column heading and
(2)Find the common zeros for all the 4 agents: ie. ids unexposed (0's) to any of the 4 agents and
place the corresponding agent. The zeros are the ones commmon to all the agents. There are 6 id's unexposed(0's) 
to the agents. ie. each one has 6 zero's.
In summary: a1: 9 exposures(1's) and 6 unexposures(0)-total 15 obs; a2:10 exposures(1's) and 
the 6 zero's-total 16 obs; a3: 7  1's and 6 zero's-total 13 obs and a4: 6  1's and 6 zero; total 12 obs.


My SAS SQL code to place the 1's under each agent is inappropriate. The only good one is a1 having only
1's.  Please I would need help here to place the 1's under their corresponding agent.There was an 
error message-the global statement not supported.......

The SAS code and log are found below' the results are attached.

Thanks in advance for your help.

data d1;
input id$ a1 a2 a3 a4  lung$ 14-21;
datalines;
os1  1 0 0 1 ca case  
os2  1 1 0 0 ca case  
os3  0 0 0 0 pop cont 
os4  1 0 0 1 pop cont 
os5  0 1 0 0 ca case  
os6  0 0 0 0 ca case  
os7  1 0 1 1 pop cont 
os8  0 1 0 0 ca case  
os9  1 0 1 0 pop cont 
os10 0 0 1 0 ca case  
os11 0 1 0 0 pop cont 
os12 0 1 0 0 pop cont 
os13 1 1 1 1 pop cont  
os14 0 0 0 0 pop cont 
os15 1 0 0 1 ca case  
os16 0 1 1 0 pop cont 
os17 1 1 1 1 pop cont  
os18 0 0 0 0 ca case  
os19 0 1 0 0 pop cont  
os20 0 0 0 0 ca case 
os21 0 0 0 0 ca case 
os22 1 1 1 0 ca case
;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 data d1;

74 input id$ a1 a2 a3 a4 lung$ 14-21;

75 datalines;

NOTE: The data set WORK.D1 has 22 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

98 ;

100

101 /* Step 1: Placing exposed (1's) under each agent*/

102

103 /* Frequencies of the exposed (1's)*/

104 proc freq data=d1;

105 tables a1* lung a2* lung a3*lung a4*lung;

106 title 'Table 1: frequencies of exposures';

107 run;

NOTE: There were 22 observations read from the data set WORK.D1.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.35 seconds

cpu time 0.34 seconds

108

109 data ones; set d1;

NOTE: There were 22 observations read from the data set WORK.D1.

NOTE: The data set WORK.ONES has 22 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

110 proc sql;

111 select

112 id, a1, a2, a3,a4,lung from d1 where a1=1;

113 where a2=1; where a3=1; where a4=1;

WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

114

115 /*Step 2:(a) Finding ids unexposed to 4 agents*/

116

NOTE: PROCEDURE SQL used (Total process time):

real time 0.09 seconds

cpu time 0.09 seconds

117 proc freq data=d1(where=(sum(a1,a2,a3,a4)=0));

118 tables lung;

119 title 'Table 2:Subjects unexposed to any of the 4 agents';

120 run;

NOTE: There were 6 observations read from the data set WORK.D1.

WHERE SUM(a1, a2, a3, a4)=0;

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.18 seconds

cpu time 0.18 seconds

121

122 /*(b) Using SQL approach to confirm results of (a) above*/

123 proc sql;

124 create table t as

125 select

126 id, a1, a2, a3,a4,lung,

127 sum(a1,a2,a3,a4)=0 as ue1234

128 from d1

129 ;

NOTE: Table WORK.T created, with 22 rows and 7 columns.

130 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.03 seconds

cpu time 0.01 seconds

131 proc print data=t;

132 title 'Table 3: The 1's are id's unexposed to agents; zeros here are the exposed';

________________

WARNING: The TITLE statement is ambiguous due to invalid options or unquoted text.

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space

between a quoted string and the succeeding identifier is recommended.

133 run;

NOTE: There were 22 observations read from the data set WORK.T.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.19 seconds

cpu time 0.20 seconds

134

135 /* Step 3(Last step) : Placing the Unexposed (0's) under exposed (1's) of each agent*/

136

137 /* Not sure what to do here,please*/

138

139

140

141 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

153

Re: SQL or any appropriate method to select elements under columns and common exposures

Kurt_Bremser — Sat, 10 Oct 2020 06:31:45 GMT

The WHERE must be part of the SELECT, and there can be only one WHERE in a single SELECT.

For which observations do you want to select in that step? Those where only one agent is true, or those where any agent is true?

Re: SQL or any appropriate method to select elements under columns and common exposures

ak2011 — Sat, 10 Oct 2020 14:28:22 GMT

Hi,
Thanks for responding. Actually, I need to select where any agent is true. I have tried many times, and is not working. Please help.
ak.

Re: SQL or any appropriate method to select elements under columns and common exposures

Kurt_Bremser — Sat, 10 Oct 2020 15:12:37 GMT

You could write this:

where a1=1 or a2=1 or a3=1 or a4=1

but I prefer this:

where sum(a1,a2,a3,a4) > 0

topic Re: SQL or any appropriate method to select elements under columns and common exposures in SAS Studio

SQL or any appropriate method to select elements under columns and common exposures

Re: SQL or any appropriate method to select elements under columns and common exposures

Re: SQL or any appropriate method to select elements under columns and common exposures

Re: SQL or any appropriate method to select elements under columns and common exposures