Reading CSV file - log reads all observations then loses them after data step

[erinljohnson]

I'm trying to read a CSV file with a mix of character, numeric, and date variables. Based on the code below, the log says that SAS creates a dataset with 722 observations and 69 variables. Then, it reads my data step and says there was 1 observation read and 2 variables. I think it doesn't like my if then statements but I don't know how to rewrite. Why does this happen? Please help me correct this error. My log is listed below the code. Also, I use SAS University 9.4. 

libname elj '/folders/myfolders/';
proc import datafile = '/folders/myfolders/mhttc1.csv'
out=mhttc
dbms=csv
replace;
run;
proc format;
value place 1='NE'
2='IA'
3='MO'
4='KS'
5='NM'
6='Online';
data shells;
length location $18.;
set elj.mhttc (keep= location);

if location = 'Chadron, Nebraska' or location = 'LaVista, Nebraska' or location = 'Lincoln, NE' or location = 'Omaha, NE' or location = 'wayne' or location = 'Wayne, Nebraska'
then place = 1;
else if location = 'Council Bluffs, IA' or location = 'Des Moines, IA' or location = 'Knoxville, IA'
then place = 2;
else if location = 'Columbia, MO'
then place = 3;
else if location = 'Kansas City, KS'
then place = 4;
else if location = 'SantaFe, NM'
then place = 5;
else if location = 'online'
then place = 6;

format place place.;
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73 libname elj '/folders/myfolders/';

NOTE: Libref ELJ was successfully assigned as follows:

Engine: V9

Physical Name: /folders/myfolders

74 proc import datafile = '/folders/myfolders/mhttc1.csv'

75 out=mhttc

76 dbms=csv

77 replace;

78 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

79 /**********************************************************************

80 * PRODUCT: SAS

81 * VERSION: 9.4

82 * CREATOR: External File Interface

83 * DATE: 05JAN20

84 * DESC: Generated SAS Datastep Code

85 * TEMPLATE SOURCE: (None Specified.)

86 ***********************************************************************/

87 data WORK.MHTTC ;

88 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

89 infile '/folders/myfolders/mhttc1.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

90 informat GrantNo $8. ;

91 informat EventCode $10. ;

92 informat EventType $1. ;

93 informat Topic $21. ;

94 informat Location $14. ;

95 informat EventTitle $21. ;

96 informat EventDate mmddyy10. ;

97 informat FFY best32. ;

98 informat Quarter best32. ;

99 informat Month best32. ;

100 informat PersonID $4. ;

101 informat QualGeneral best32. ;

102 informat QualInfo best32. ;

103 informat QualMaterials best32. ;

104 informat QualExperience best32. ;

105 informat QualOrg best32. ;

106 informat QualUseful best32. ;

107 informat StaffKnowl best32. ;

108 informat StaffPrep best32. ;

109 informat StaffRecept best32. ;

110 informat UserEffective best32. ;

111 informat UserEnhance best32. ;

112 informat UserRelevant best32. ;

113 informat UseInfo best32. ;

114 informat UseBenefit best32. ;

115 informat Relevant best32. ;

116 informat Recommend best32. ;

117 informat AdeqKnowl best32. ;

118 informat HasSkill best32. ;

119 informat Useful best32. ;

120 informat JobTitle best32. ;

121 informat JobTitleOtherSpec best32. ;

122 informat OrgDesc best32. ;

123 informat OrgDescOtherSpec best32. ;

124 informat Gender best32. ;

125 informat Hispanic best32. ;

126 informat Black $1. ;

127 informat Asian $1. ;

128 informat White best32. ;

129 informat AlaskaNative $1. ;

130 informat AmericanIndian best32. ;

131 informat NatHawaiianPacificIsl $1. ;

132 informat NativeHawaiian $1. ;

133 informat PacificIslander $1. ;

134 informat RaceOtherSpec $1. ;

135 informat Degree best32. ;

136 informat DegreeOtherSpec $1. ;

137 informat PrimaryProf best32. ;

138 informat PrimaryProfOtherSpec $25. ;

139 informat FieldStudy best32. ;

140 informat FieldStudyOtherSpec $1. ;

141 informat NotLicensed best32. ;

142 informat AddictionsPrev best32. ;

143 informat Counseling best32. ;

144 informat Dentistry $1. ;

145 informat Medicine $1. ;

146 informat Nursing $1. ;

147 informat Pharmacology $1. ;

148 informat Psychology $1. ;

149 informat SocialWork best32. ;

150 informat LicenseOtherSpec $15. ;

151 informat WorkRole best32. ;

152 informat WorkRoleOtherSpec $23. ;

153 informat WorkSetting best32. ;

154 informat WorkSettingOtherSpec $29. ;

155 informat WorkZipCode best32. ;

156 informat MostUseful $230. ;

157 informat Improve $95. ;

158 informat PostEventCreateDate mmddyy10. ;

159 format GrantNo $8. ;

160 format EventCode $10. ;

161 format EventType $1. ;

162 format Topic $21. ;

163 format Location $14. ;

164 format EventTitle $21. ;

165 format EventDate mmddyy10. ;

166 format FFY best12. ;

167 format Quarter best12. ;

168 format Month best12. ;

169 format PersonID $4. ;

170 format QualGeneral best12. ;

171 format QualInfo best12. ;

172 format QualMaterials best12. ;

173 format QualExperience best12. ;

174 format QualOrg best12. ;

175 format QualUseful best12. ;

176 format StaffKnowl best12. ;

177 format StaffPrep best12. ;

178 format StaffRecept best12. ;

179 format UserEffective best12. ;

180 format UserEnhance best12. ;

181 format UserRelevant best12. ;

182 format UseInfo best12. ;

183 format UseBenefit best12. ;

184 format Relevant best12. ;

185 format Recommend best12. ;

186 format AdeqKnowl best12. ;

187 format HasSkill best12. ;

188 format Useful best12. ;

189 format JobTitle best12. ;

190 format JobTitleOtherSpec best12. ;

191 format OrgDesc best12. ;

192 format OrgDescOtherSpec best12. ;

193 format Gender best12. ;

194 format Hispanic best12. ;

195 format Black $1. ;

196 format Asian $1. ;

197 format White best12. ;

198 format AlaskaNative $1. ;

199 format AmericanIndian best12. ;

200 format NatHawaiianPacificIsl $1. ;

201 format NativeHawaiian $1. ;

202 format PacificIslander $1. ;

203 format RaceOtherSpec $1. ;

204 format Degree best12. ;

205 format DegreeOtherSpec $1. ;

206 format PrimaryProf best12. ;

207 format PrimaryProfOtherSpec $25. ;

208 format FieldStudy best12. ;

209 format FieldStudyOtherSpec $1. ;

210 format NotLicensed best12. ;

211 format AddictionsPrev best12. ;

212 format Counseling best12. ;

213 format Dentistry $1. ;

214 format Medicine $1. ;

215 format Nursing $1. ;

216 format Pharmacology $1. ;

217 format Psychology $1. ;

218 format SocialWork best12. ;

219 format LicenseOtherSpec $15. ;

220 format WorkRole best12. ;

221 format WorkRoleOtherSpec $23. ;

222 format WorkSetting best12. ;

223 format WorkSettingOtherSpec $29. ;

224 format WorkZipCode best12. ;

225 format MostUseful $230. ;

226 format Improve $95. ;

227 format PostEventCreateDate mmddyy10. ;

228 input

229 GrantNo $

230 EventCode $

231 EventType $

232 Topic $

233 Location $

234 EventTitle $

235 EventDate

236 FFY

237 Quarter

238 Month

239 PersonID $

240 QualGeneral

241 QualInfo

242 QualMaterials

243 QualExperience

244 QualOrg

245 QualUseful

246 StaffKnowl

247 StaffPrep

248 StaffRecept

249 UserEffective

250 UserEnhance

251 UserRelevant

252 UseInfo

253 UseBenefit

254 Relevant

255 Recommend

256 AdeqKnowl

257 HasSkill

258 Useful

259 JobTitle

260 JobTitleOtherSpec

261 OrgDesc

262 OrgDescOtherSpec

263 Gender

264 Hispanic

265 Black $

266 Asian $

267 White

268 AlaskaNative $

269 AmericanIndian

270 NatHawaiianPacificIsl $

271 NativeHawaiian $

272 PacificIslander $

273 RaceOtherSpec $

274 Degree

275 DegreeOtherSpec $

276 PrimaryProf

277 PrimaryProfOtherSpec $

278 FieldStudy

279 FieldStudyOtherSpec $

280 NotLicensed

281 AddictionsPrev

282 Counseling

283 Dentistry $

284 Medicine $

285 Nursing $

286 Pharmacology $

287 Psychology $

288 SocialWork

289 LicenseOtherSpec $

290 WorkRole

291 WorkRoleOtherSpec $

292 WorkSetting

293 WorkSettingOtherSpec $

294 WorkZipCode

295 MostUseful $

296 Improve $

297 PostEventCreateDate

298 ;

299 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

300 run;

NOTE: The infile '/folders/myfolders/mhttc1.csv' is:

Filename=/folders/myfolders/mhttc1.csv,

Owner Name=root,Group Name=vboxsf,

Access Permission=-rwxrwx---,

Last Modified=17Dec2019:19:24:56,

File Size (bytes)=278668

NOTE: 772 records were read from the infile '/folders/myfolders/mhttc1.csv'.

The minimum record length was 197.

The maximum record length was 965.

NOTE: The data set WORK.MHTTC has 772 observations and 69 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.02 seconds

772 rows created in WORK.MHTTC from /folders/myfolders/mhttc1.csv.

NOTE: WORK.MHTTC data set was successfully created.

NOTE: The data set WORK.MHTTC has 772 observations and 69 variables.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.52 seconds

cpu time 0.44 seconds

301 proc format;

302 value place 1='NE'

303 2='IA'

304 3='MO'

305 4='KS'

306 5='NM'

307 6='Online';

NOTE: Format PLACE has been output.

NOTE: PROCEDURE FORMAT used (Total process time):

real time 0.02 seconds

cpu time 0.03 seconds

308 data shells;

309 length location $18.;

310 set elj.mhttc (keep= location);

311

312 if location = 'Chadron, Nebraska' or location = 'LaVista, Nebraska' or location = 'Lincoln, NE' or location = 'Omaha, NE'

312 ! or location = 'wayne' or location = 'Wayne, Nebraska'

313 then place = 1;

314 else if location = 'Council Bluffs, IA' or location = 'Des Moines, IA' or location = 'Knoxville, IA'

315 then place = 2;

316 else if location = 'Columbia, MO'

317 then place = 3;

318 else if location = 'Kansas City, KS'

319 then place = 4;

320 else if location = 'SantaFe, NM'

321 then place = 5;

322 else if location = 'online'

323 then place = 6;

324

325 format place place.;

326 run;

NOTE: Format $LOCATION was not found or could not be loaded.

NOTE: There were 1 observations read from the data set ELJ.MHTTC.

NOTE: The data set WORK.SHELLS has 1 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.00 seconds

327

328

329

330 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

[Kurt_Bremser]

Compare this:

NOTE: WORK.MHTTC data set was successfully created.

with this:

NOTE: There were 1 observations read from the data set ELJ.MHTTC.

See the important difference?

[erinljohnson]

Got it. Thank you!!

[novinosrin · Accepted solution]

HI @erinljohnson   Your log says that it read a CSV file aka /folders/myfolders/mhttc1.csv.and created a 

1. SAS dataset with 772 rows created in WORK.MHTTC from /folders/myfolders/mhttc1.csv.

So,this make me comprehend and conclude that your MHTTC dataset is a dataset in the WORK library

2. However your subsequent datastep seems to read the dataset MHTTC apparently from a different library by the name ELJ as seen in set elj.mhttc (keep= location);?

data shells;
length location $18.;
set elj.mhttc (keep= location);  /*Please notice here*/

Is WORK.MHTTC  and ELJ.MHTTC  the same? I mean , are these two merely copies of the same contents of values and variables?

Can you check plz?

[erinljohnson]

Thank you for the response!! I eliminated the libname statement and connected work to the set statement based on your question. I have no errors from this code. Thanks again 🙂

proc import datafile = '/folders/myfolders/mhttc1.csv'
out=mhttc
dbms=csv
replace;
run;
proc format;
value place 1='NE'
2='IA'
3='MO'
4='KS'
5='NM'
6='Online';
data shells;
length location $18.;
set work.mhttc (keep= location);

if location = 'Chadron, Nebraska' or location = 'LaVista, Nebraska' or location = 'Lincoln, NE' or location = 'Omaha, NE' or location = 'wayne' or location = 'Wayne, Nebraska'
then place = 1;
else if location = 'Council Bluffs, IA' or location = 'Des Moines, IA' or location = 'Knoxville, IA'
then place = 2;
else if location = 'Columbia, MO'
then place = 3;
else if location = 'Kansas City, KS'
then place = 4;
else if location = 'SantaFe, NM'
then place = 5;
else if location = 'online'
then place = 6;

format place place.;
run;
proc contents data=shells;
run;

[PGStats]

The dataset mhttc was created in library WORK by the import procedure but your datastep reads dataset mhttc from library elj.

[erinljohnson]

Based on this info, I changed a little code and figured it out. The working code is below. Thank you so much! 🙂

proc import datafile = '/folders/myfolders/mhttc1.csv'
out=mhttc
dbms=csv
replace;
run;
proc format;
value place 1='NE'
2='IA'
3='MO'
4='KS'
5='NM'
6='Online';
data shells;
length location $18.;
set work.mhttc (keep= location);

if location = 'Chadron, Nebraska' or location = 'LaVista, Nebraska' or location = 'Lincoln, NE' or location = 'Omaha, NE' or location = 'wayne' or location = 'Wayne, Nebraska'
then place = 1;
else if location = 'Council Bluffs, IA' or location = 'Des Moines, IA' or location = 'Knoxville, IA'
then place = 2;
else if location = 'Columbia, MO'
then place = 3;
else if location = 'Kansas City, KS'
then place = 4;
else if location = 'SantaFe, NM'
then place = 5;
else if location = 'online'
then place = 6;

format place place.;
run;
proc contents data=shells;
run;