Deleting missing values in merged file

ak2011 · Posted 11-27-2019 06:19 AM

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;

72

73

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.

Kurt_Bremser · Posted 11-27-2019 08:13 AM

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

ak2011 · Posted 11-29-2019 04:25 AM

Yes, you are right! Thanks.

Deleting missing values in merged file

Re: Deleting missing values in merged file

Re: Deleting missing values in merged file

Deleting missing values in merged file

Re: Deleting missing values in merged file

Re: Deleting missing values in merged file

2025 SAS Hackathon: There is still time!

SAS Training: Just a Click Away