Deleting missing values in merged file

ak2011 — Wed, 27 Nov 2019 11:19:26 GMT

Hi,

May I please know the SAS code to place zeros under ali_exp and iso_exp variables ( in the merged file) of the attached file? I was expecting SAS to place zeros there since it is either exposed(1) or not exposed(0). Thanks.

/* Reading 6 different idchems data*/
data t1;
input id$ job idchem;
datalines;
os1 1 990005
os1 1 990021
os1 1 211700
os1 2 211700
os1 2 990021
os1 2 210701
os1 2 990005
os2 1 210701
os2 1 990005
os2 2 990021
os2 3 210701
os2 3 990005
os3 3 210701
os3 1 211700
os4 1 210701
os4 1 990005
os4 1 211700
os5 1 520299
os5 1 420204
os6 2 520299
os6 3 420204
os6 4 990005
os7 1 520299
;
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

74 /* Reading 6 different idchems data*/

75 data t1;

76 input id$ job idchem;

77 datalines;

NOTE: The data set WORK.T1 has 23 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

101 ;

102 run;

103 proc print; run;

NOTE: There were 23 observations read from the data set WORK.T1.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.20 seconds

cpu time 0.20 seconds

104

105 /* 1st part of work*/

106 data t1p1;

107 set t1;

108 if idchem ne 990005 and idchem ne 990021 and idchem ne 210701 and idchem ne 211700 then delete; run;

NOTE: There were 23 observations read from the data set WORK.T1.

NOTE: The data set WORK.T1P1 has 18 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

109

110 /*TRANSPOSING VARIABLES*/

111

112 proc sort data=t1p1; by id job;

NOTE: There were 18 observations read from the data set WORK.T1P1.

NOTE: The data set WORK.T1P1 has 18 observations and 3 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

113 proc transpose data=t1p1 out=t1p1b prefix=idchem;

114 by id job;

115 var idchem;

116 run;

NOTE: There were 18 observations read from the data set WORK.T1P1.

NOTE: The data set WORK.T1P1B has 9 observations and 7 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

real time 0.02 seconds

cpu time 0.03 seconds

117

118 /*cla*/

119 data clan;

120 set t1p1b;

121 if idchem1=990005 or idchem2=990005 or idchem3=990005 or idchem4=990005 then cla_exp=1;

122 else cla_exp=0;

123 put _all_; run;

id=os1 job=1 _NAME_=idchem idchem1=990005 idchem2=990021 idchem3=211700 idchem4=. cla_exp=1 _ERROR_=0 _N_=1

id=os1 job=2 _NAME_=idchem idchem1=211700 idchem2=990021 idchem3=210701 idchem4=990005 cla_exp=1 _ERROR_=0 _N_=2

id=os2 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. cla_exp=1 _ERROR_=0 _N_=3

id=os2 job=2 _NAME_=idchem idchem1=990021 idchem2=. idchem3=. idchem4=. cla_exp=0 _ERROR_=0 _N_=4

id=os2 job=3 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. cla_exp=1 _ERROR_=0 _N_=5

id=os3 job=1 _NAME_=idchem idchem1=211700 idchem2=. idchem3=. idchem4=. cla_exp=0 _ERROR_=0 _N_=6

id=os3 job=3 _NAME_=idchem idchem1=210701 idchem2=. idchem3=. idchem4=. cla_exp=0 _ERROR_=0 _N_=7

id=os4 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=211700 idchem4=. cla_exp=1 _ERROR_=0 _N_=8

id=os6 job=4 _NAME_=idchem idchem1=990005 idchem2=. idchem3=. idchem4=. cla_exp=1 _ERROR_=0 _N_=9

NOTE: There were 9 observations read from the data set WORK.T1P1B.

NOTE: The data set WORK.CLAN has 9 observations and 8 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

124 proc print;

125 Title "cla exposure";

126 run;

NOTE: There were 9 observations read from the data set WORK.CLAN.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.26 seconds

cpu time 0.26 seconds

127

128 /*%ge id-job-Cla*/

129 proc freq data=clan;

130 table cla_exp;

131 run;

NOTE: There were 9 observations read from the data set WORK.CLAN.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.15 seconds

cpu time 0.14 seconds

132

133

134 /*Bio*/

135 data bion;

136 set t1p1b;

137 if idchem1=990021 or idchem2=990021 or idchem3=990021 or idchem4=990021 then bio_exp=1; else bio_exp=0;

138 id_job=catx('_', id, job);

139 put _all_;run;

id=os1 job=1 _NAME_=idchem idchem1=990005 idchem2=990021 idchem3=211700 idchem4=. bio_exp=1 id_job=os1_1 _ERROR_=0 _N_=1

id=os1 job=2 _NAME_=idchem idchem1=211700 idchem2=990021 idchem3=210701 idchem4=990005 bio_exp=1 id_job=os1_2 _ERROR_=0 _N_=2

id=os2 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. bio_exp=0 id_job=os2_1 _ERROR_=0 _N_=3

id=os2 job=2 _NAME_=idchem idchem1=990021 idchem2=. idchem3=. idchem4=. bio_exp=1 id_job=os2_2 _ERROR_=0 _N_=4

id=os2 job=3 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. bio_exp=0 id_job=os2_3 _ERROR_=0 _N_=5

id=os3 job=1 _NAME_=idchem idchem1=211700 idchem2=. idchem3=. idchem4=. bio_exp=0 id_job=os3_1 _ERROR_=0 _N_=6

id=os3 job=3 _NAME_=idchem idchem1=210701 idchem2=. idchem3=. idchem4=. bio_exp=0 id_job=os3_3 _ERROR_=0 _N_=7

id=os4 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=211700 idchem4=. bio_exp=0 id_job=os4_1 _ERROR_=0 _N_=8

id=os6 job=4 _NAME_=idchem idchem1=990005 idchem2=. idchem3=. idchem4=. bio_exp=0 id_job=os6_4 _ERROR_=0 _N_=9

NOTE: There were 9 observations read from the data set WORK.T1P1B.

NOTE: The data set WORK.BION has 9 observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

140 proc print;

141 Title "bio exposure";

142 run;

NOTE: There were 9 observations read from the data set WORK.BION.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.13 seconds

cpu time 0.13 seconds

143

144

145 /*%ge id-job-bio*/

146 proc freq data=bion;

147 table bio_exp;

148 run;

NOTE: There were 9 observations read from the data set WORK.BION.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.10 seconds

cpu time 0.09 seconds

149

150

151 /* amo*/

152 data amon;

153 set t1p1b;

154 if idchem1=210701 or idchem2=210701 or idchem3=210701 or idchem4=210701 then amo_exp=1; else amo_exp=0;

155 id_job=catx('_', id, job);

156 put _all_;run;

id=os1 job=1 _NAME_=idchem idchem1=990005 idchem2=990021 idchem3=211700 idchem4=. amo_exp=0 id_job=os1_1 _ERROR_=0 _N_=1

id=os1 job=2 _NAME_=idchem idchem1=211700 idchem2=990021 idchem3=210701 idchem4=990005 amo_exp=1 id_job=os1_2 _ERROR_=0 _N_=2

id=os2 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. amo_exp=1 id_job=os2_1 _ERROR_=0 _N_=3

id=os2 job=2 _NAME_=idchem idchem1=990021 idchem2=. idchem3=. idchem4=. amo_exp=0 id_job=os2_2 _ERROR_=0 _N_=4

id=os2 job=3 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. amo_exp=1 id_job=os2_3 _ERROR_=0 _N_=5

id=os3 job=1 _NAME_=idchem idchem1=211700 idchem2=. idchem3=. idchem4=. amo_exp=0 id_job=os3_1 _ERROR_=0 _N_=6

id=os3 job=3 _NAME_=idchem idchem1=210701 idchem2=. idchem3=. idchem4=. amo_exp=1 id_job=os3_3 _ERROR_=0 _N_=7

id=os4 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=211700 idchem4=. amo_exp=1 id_job=os4_1 _ERROR_=0 _N_=8

id=os6 job=4 _NAME_=idchem idchem1=990005 idchem2=. idchem3=. idchem4=. amo_exp=0 id_job=os6_4 _ERROR_=0 _N_=9

NOTE: There were 9 observations read from the data set WORK.T1P1B.

NOTE: The data set WORK.AMON has 9 observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

157 proc print data=amon;

158 Title "amo exposure";

159 run;

NOTE: There were 9 observations read from the data set WORK.AMON.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.15 seconds

cpu time 0.16 seconds

160

161 /*%ge id-job-amo*/

162 proc freq data=amon;

163 table amo_exp;

164 run;

NOTE: There were 9 observations read from the data set WORK.AMON.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.10 seconds

cpu time 0.09 seconds

165

166 /* chl*/

167 data chln;

168 set t1p1b;

169 if idchem1=211700 or idchem2=211700 or idchem3=211700 or idchem4=211700 then chl_exp=1; else chl_exp=0;

170 id_job=catx('_', id, job);

171 put _all_; run;

id=os1 job=1 _NAME_=idchem idchem1=990005 idchem2=990021 idchem3=211700 idchem4=. chl_exp=1 id_job=os1_1 _ERROR_=0 _N_=1

id=os1 job=2 _NAME_=idchem idchem1=211700 idchem2=990021 idchem3=210701 idchem4=990005 chl_exp=1 id_job=os1_2 _ERROR_=0 _N_=2

id=os2 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. chl_exp=0 id_job=os2_1 _ERROR_=0 _N_=3

id=os2 job=2 _NAME_=idchem idchem1=990021 idchem2=. idchem3=. idchem4=. chl_exp=0 id_job=os2_2 _ERROR_=0 _N_=4

id=os2 job=3 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=. idchem4=. chl_exp=0 id_job=os2_3 _ERROR_=0 _N_=5

id=os3 job=1 _NAME_=idchem idchem1=211700 idchem2=. idchem3=. idchem4=. chl_exp=1 id_job=os3_1 _ERROR_=0 _N_=6

id=os3 job=3 _NAME_=idchem idchem1=210701 idchem2=. idchem3=. idchem4=. chl_exp=0 id_job=os3_3 _ERROR_=0 _N_=7

id=os4 job=1 _NAME_=idchem idchem1=210701 idchem2=990005 idchem3=211700 idchem4=. chl_exp=1 id_job=os4_1 _ERROR_=0 _N_=8

id=os6 job=4 _NAME_=idchem idchem1=990005 idchem2=. idchem3=. idchem4=. chl_exp=0 id_job=os6_4 _ERROR_=0 _N_=9

NOTE: There were 9 observations read from the data set WORK.T1P1B.

NOTE: The data set WORK.CHLN has 9 observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

172

173 proc print data=chln;

174 Title"chl exposure";

175 run;

NOTE: There were 9 observations read from the data set WORK.CHLN.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.15 seconds

cpu time 0.15 seconds

176

177 /*%ge id-job-chl*/

178 proc freq data=chln;

179 table chl_exp;

180 run;

NOTE: There were 9 observations read from the data set WORK.CHLN.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.11 seconds

cpu time 0.10 seconds

181

182

183 /*2nd part of work*/

184 data t2;

185 set t1;

186 if idchem ne 520299 and idchem ne 420204 then delete; run;

NOTE: There were 23 observations read from the data set WORK.T1.

NOTE: The data set WORK.T2 has 5 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.04 seconds

187 proc sort data=t2; by id job;

NOTE: There were 5 observations read from the data set WORK.T2.

NOTE: The data set WORK.T2 has 5 observations and 3 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.04 seconds

cpu time 0.04 seconds

188 proc transpose data=t2 out=t2b prefix=idchem;

189 by id job;

190 /*id job;*/

191 var idchem;

192 run;

NOTE: There were 5 observations read from the data set WORK.T2.

NOTE: The data set WORK.T2B has 4 observations and 5 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

real time 0.02 seconds

cpu time 0.02 seconds

193

194 /*ali*/

195 data alin;

196 set t2b;

197 if idchem1= 520299 or idchem2=520299 then ali_exp=1;

198 else ali_exp=0;

199 id_job=catx('_', id, job);

200 put _all_;

201 run;

id=os5 job=1 _NAME_=idchem idchem1=520299 idchem2=420204 ali_exp=1 id_job=os5_1 _ERROR_=0 _N_=1

id=os6 job=2 _NAME_=idchem idchem1=520299 idchem2=. ali_exp=1 id_job=os6_2 _ERROR_=0 _N_=2

id=os6 job=3 _NAME_=idchem idchem1=420204 idchem2=. ali_exp=0 id_job=os6_3 _ERROR_=0 _N_=3

id=os7 job=1 _NAME_=idchem idchem1=520299 idchem2=. ali_exp=1 id_job=os7_1 _ERROR_=0 _N_=4

NOTE: There were 4 observations read from the data set WORK.T2B.

NOTE: The data set WORK.ALIN has 4 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

202 proc print data=alin;

203 Title "ali exposure";

204 run;

NOTE: There were 4 observations read from the data set WORK.ALIN.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.11 seconds

cpu time 0.10 seconds

205

206 /*%ge id-job-ali*/

207 proc freq data=alin;

208 table ali_exp;

209 run;

NOTE: There were 4 observations read from the data set WORK.ALIN.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.11 seconds

cpu time 0.09 seconds

210

211

212 /*iso exposure*/

213 data ison;

214 set t2b;

215 if idchem1=420204 or idchem2=420204 then iso_exp=1;

216 else iso_exp=0;

217 id_job=catx('_', id, job);

218 put _all_;

219 run;

id=os5 job=1 _NAME_=idchem idchem1=520299 idchem2=420204 iso_exp=1 id_job=os5_1 _ERROR_=0 _N_=1

id=os6 job=2 _NAME_=idchem idchem1=520299 idchem2=. iso_exp=0 id_job=os6_2 _ERROR_=0 _N_=2

id=os6 job=3 _NAME_=idchem idchem1=420204 idchem2=. iso_exp=1 id_job=os6_3 _ERROR_=0 _N_=3

id=os7 job=1 _NAME_=idchem idchem1=520299 idchem2=. iso_exp=0 id_job=os7_1 _ERROR_=0 _N_=4

NOTE: There were 4 observations read from the data set WORK.T2B.

NOTE: The data set WORK.ISON has 4 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

220 proc print;

221 Title "iso exposure";

222

223 /*%ge id-job-iso*/

NOTE: There were 4 observations read from the data set WORK.ISON.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.12 seconds

cpu time 0.11 seconds

224 proc freq data=ison;

225 table iso_exp;

226 run;

NOTE: There were 4 observations read from the data set WORK.ISON.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.10 seconds

cpu time 0.09 seconds

227

228 /* Merging the 6 different idchem files*/

229

230 data mn; merge clan bion amon chln alin ison;

231 drop _name_;

232 run;

NOTE: There were 9 observations read from the data set WORK.CLAN.

NOTE: There were 9 observations read from the data set WORK.BION.

NOTE: There were 9 observations read from the data set WORK.AMON.

NOTE: There were 9 observations read from the data set WORK.CHLN.

NOTE: There were 4 observations read from the data set WORK.ALIN.

NOTE: There were 4 observations read from the data set WORK.ISON.

NOTE: The data set WORK.MN has 9 observations and 13 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.02 seconds

233

234 proc print data=mn;

235 Title "Merged exposure files for cla ,bio, amo,chl,ali and iso pollutants";

236 run;

NOTE: There were 9 observations read from the data set WORK.MN.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.20 seconds

cpu time 0.21 seconds

237

238 proc freq data=mn;

239 tables cla_exp*bio_exp;

240 tables cla_exp*amo_exp;

241 tables cla_exp*chl_exp;

242 tables bio_exp*cla_exp;

243 tables bio_exp*amo_exp;

244 tables bio_exp*chl_exp;

245 run;

NOTE: There were 9 observations read from the data set WORK.MN.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.53 seconds

cpu time 0.52 seconds

246

247

248

249

250 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

262

Thanks in advance.

ak.

Re: Deleting missing values in merged file

Kurt_Bremser — Wed, 27 Nov 2019 13:13:45 GMT

What do you want to get out of your initial dataset? To detect certain exposures for a given id (or job), the transposition is not necessary at all, you can do that much easier in a single data step with retained variables.

Re: Deleting missing values in merged file

ak2011 — Fri, 29 Nov 2019 09:25:01 GMT

Yes, you are right! Thanks.

topic Re: Deleting missing values in merged file in SAS Studio

Deleting missing values in merged file

Re: Deleting missing values in merged file

Re: Deleting missing values in merged file