BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
erinljohnson
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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?
 

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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
Calcite | Level 5

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
Opal | Level 21

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

PG
erinljohnson
Calcite | Level 5
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 899 views
  • 0 likes
  • 4 in conversation