topic Re: how to read the csv file? in SAS Programming

how to read the csv file?

tianerhu — Wed, 19 May 2021 23:31:26 GMT

The following is a csv file

Name,Hire Date,Company,Country,Date of Birth
Gisela S. Santos,8/12/17,Pede Nunc Sed Limited,Micronesia,8/21/1971
Maxwell L. Cooley,9/4/17,A LLP,Somalia,4/30/1975
Thane P. Obrien,10/28/17,Consectetuer Limited,Jamaica,4/23/1988
Minerva C. Conley,1/5/18,Feugiat Tellus Lorem Institute,Fiji,2/18/1975
Kylee R. Finch,10/31/17,Magna Incorporated,Myanmar,5/18/1973
Calista F. Chambers,9/11/17,Facilisis Incorporated,Libya,3/4/1972
Fuller X. Bradford,1/27/18,Morbi Incorporated,Saint Pierre and Miquelon,12/1/1976

the following is my code:

data ABC;
infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base
\practice data\cert\new_hires.csv'   dsd firstobs=2;
input  name $16. hire_date company $ country $ date_of_birth;
run;
proc print data = ABC;
run;

the following is output:

how to read the hire_date and date_of_birth ?

Re: how to read the csv file?

Reeza — Wed, 19 May 2021 23:45:58 GMT

You haven't specified the informats for your date variables. Add an INFORMAT statement before the INPUT to tell SAS what the format of the date variable is in the file. You may also want a format statement so it shows up as a date.

informat hire_date date_of_birth mmddyy10.;
format hire_date date_of_birth ddmmyyd10.;

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:12:14 GMT

does not work.

Re: how to read the csv file?

SASKiwi — Thu, 20 May 2021 00:19:13 GMT

Please post the complete SAS log of your program so we can see more clearly what is happening.

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:21:30 GMT

138 data ABC;
139 infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base
140 \practice data\cert\new_hires.csv' dsd firstobs=2;
141 input name $16. hire_date company $ country $ date_of_birth;
142 informat hire_date date_of_birth mmddyy10.;
143 format hire_date date_of_birth mmddyy10.;
144 run;

NOTE: The infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base\practice
data\cert\new_hires.csv' is:

Filename=C:\Users\liaodong\Documents\My SAS Files\prep guide\base\practice
data\cert\new_hires.csv,
RECFM=V,LRECL=32767,File Size (bytes)=15802,
Last Modified=07May2021:17:08:56,
Create Time=11Jan2021:18:37:55

NOTE: Invalid data for date_of_birth in line 2 48-57.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9
2 Gisela S. Santos,8/12/17,Pede Nunc Sed Limited,Micronesia,8/21/1971 80
name=Gisela S. Santos hire_date=. company=8/12/17 country=Pede Nun date_of_birth=. _ERROR_=1 _N_=1
NOTE: Invalid data for hire_date in line 3 17-17.
NOTE: Invalid data for date_of_birth in line 3 32-38.
3 Maxwell L. Cooley,9/4/17,A LLP,Somalia,4/30/1975 80
name=Maxwell L. Coole hire_date=. company=9/4/17 country=A LLP date_of_birth=. _ERROR_=1 _N_=2
NOTE: Invalid data for hire_date in line 5 17-17.
NOTE: Invalid data for date_of_birth in line 5 57-60.
5 Minerva C. Conley,1/5/18,Feugiat Tellus Lorem Institute,Fiji,2/18/1975 80
name=Minerva C. Conle hire_date=. company=1/5/18 country=Feugiat date_of_birth=. _ERROR_=1 _N_=4
NOTE: Invalid data for hire_date in line 6 17-23.
6 Kylee R. Finch,10/31/17,Magna Incorporated,Myanmar,5/18/1973 80
name=Kylee R. Finch,1 hire_date=. company=Magna In country=Myanmar date_of_birth=05/18/1973
_ERROR_=1 _N_=5
NOTE: Invalid data for hire_date in line 7 17-19.
NOTE: Invalid data for date_of_birth in line 7 52-56.
7 Calista F. Chambers,9/11/17,Facilisis Incorporated,Libya,3/4/1972 80
name=Calista F. Chamb hire_date=. company=9/11/17 country=Facilisi date_of_birth=. _ERROR_=1 _N_=6
NOTE: Invalid data for hire_date in line 8 17-18.
NOTE: Invalid data for date_of_birth in line 8 47-71.
8 Fuller X. Bradford,1/27/18,Morbi Incorporated,Saint Pierre and Miquelon,12/1/1976
91 160
name=Fuller X. Bradfo hire_date=. company=1/27/18 country=Morbi In date_of_birth=. _ERROR_=1 _N_=7
NOTE: Invalid data for hire_date in line 10 17-17.
NOTE: Invalid data for date_of_birth in line 10 44-48.
10 Melinda Y. Burton,2/23/18,Neque Industries,Japan,1/4/1992
91 160
name=Melinda Y. Burto hire_date=. company=2/23/18 country=Neque In date_of_birth=. _ERROR_=1 _N_=9
NOTE: Invalid data for hire_date in line 11 17-21.
11 Alan V. Gibbs,1/16/18,Metus Vitae Company,Kyrgyzstan,10/29/1982
91 160
name=Alan V. Gibbs,1/ hire_date=. company=Metus Vi country=Kyrgyzst date_of_birth=10/29/1982
_ERROR_=1 _N_=10
NOTE: Invalid data for hire_date in line 12 17-17.
NOTE: Invalid data for date_of_birth in line 12 48-51.
12 Raphael W. Carter,4/21/18,Magna Ut Corporation,Fiji,4/3/1993
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9
91 160
name=Raphael W. Carte hire_date=. company=4/21/18 country=Magna Ut date_of_birth=. _ERROR_=1 _N_=11
NOTE: Invalid data for date_of_birth in line 14 43-59.
14 Anjolie P. Finch,1/25/18,Justo Foundation,Equatorial Guinea,9/10/1972
91 160
name=Anjolie P. Finch hire_date=. company=1/25/18 country=Justo Fo date_of_birth=. _ERROR_=1 _N_=13
NOTE: Invalid data for hire_date in line 15 17-17.
NOTE: Invalid data for date_of_birth in line 15 44-53.
15 Ciaran I. Salazar,12/6/17,Ipsum Associates,Montenegro,10/23/1987
91 160
name=Ciaran I. Salaza hire_date=. company=12/6/17 country=Ipsum As date_of_birth=. _ERROR_=1 _N_=14
NOTE: Invalid data for hire_date in line 17 17-20.
NOTE: Invalid data for date_of_birth in line 17 58-65.
17 Kimberly L. Randolph,9/28/17,Quam Curabitur Incorporated,Botswana,2/1/1996
91 160
name=Kimberly L. Rand hire_date=. company=9/28/17 country=Quam Cur date_of_birth=. _ERROR_=1 _N_=16
NOTE: Invalid data for hire_date in line 18 17-17.
NOTE: Invalid data for date_of_birth in line 18 50-58.
18 Camden G. Rosales,4/18/18,Ac Eleifend Foundation,Guatemala,5/26/1976
91 160
name=Camden G. Rosale hire_date=. company=4/18/18 country=Ac Eleif date_of_birth=. _ERROR_=1 _N_=17
NOTE: Invalid data for date_of_birth in line 19 57-85.
19 Skyler D. Larsen,8/27/17,Curabitur Consequat Industries,"Virgin Islands, United States",9/
91 20/1980 160
name=Skyler D. Larsen hire_date=. company=8/27/17 country=Curabitu date_of_birth=. _ERROR_=1 _N_=18
NOTE: Invalid data for hire_date in line 20 17-21.
20 Jacob C. Noel,5/11/18,Non Lobortis Foundation,Mauritania,1/20/1976
91 160
name=Jacob C. Noel,5/ hire_date=. company=Non Lobo country=Mauritan date_of_birth=01/20/1976
_ERROR_=1 _N_=19
NOTE: Invalid data for date_of_birth in line 21 49-56.
21 Scott C. Estrada,4/9/18,Ac Eleifend Corporation,Botswana,2/14/1972
91 160
name=Scott C. Estrada hire_date=. company=4/9/18 country=Ac Eleif date_of_birth=. _ERROR_=1 _N_=20
NOTE: Invalid data for hire_date in line 22 17-17.
NOTE: Invalid data for date_of_birth in line 22 47-59.
22 Alana P. Reynolds,3/4/18,Vitae Dolor Donec PC,Liechtenstein,3/19/1991
91 160
name=Alana P. Reynold hire_date=. company=3/4/18 country=Vitae Do date_of_birth=. _ERROR_=1 _N_=21
NOTE: Invalid data for hire_date in line 23 17-19.
NOTE: Invalid data for date_of_birth in line 23 47-51.
23 Carson D. Castaneda,4/3/18,Sit Amet Massa LLP,India,9/12/1977
91 160
name=Carson D. Castan hire_date=. company=4/3/18 country=Sit Amet date_of_birth=. _ERROR_=1 _N_=22
NOTE: Invalid data for hire_date in line 24 17-21.
24 Arden W. Pena,8/29/17,Dignissim Corporation,Gambia,2/18/1988
91 160
name=Arden W. Pena,8/ hire_date=. company=Dignissi country=Gambia date_of_birth=02/18/1988 _ERROR_=1
_N_=23
NOTE: Invalid data for hire_date in line 25 17-17.
NOTE: Invalid data for date_of_birth in line 25 38-67.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
25 Laurel J. Rosales,1/29/18,Nisi Corp.,Svalbard and Jan Mayen Islands,8/4/1992
91 160
name=Laurel J. Rosale hire_date=. company=1/29/18 country=Nisi Cor date_of_birth=. _ERROR_=1 _N_=24
NOTE: 100 records were read from the infile 'C:\Users\liaodong\Documents\My SAS Files\prep
guide\base\practice data\cert\new_hires.csv'.
The minimum record length was 80.
The maximum record length was 160.
NOTE: The data set WORK.ABC has 100 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds

145 proc print data = ABC;
146 run;

NOTE: There were 100 observations read from the data set WORK.ABC.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:25:00 GMT

notice : there are no quotation mark around values of the name variable , this is the key point why this question is hard to code .

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:34:40 GMT

data ABC;
informat hire_date date_of_birth mmddyy10.;

infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base
\practice data\cert\new_hires.csv'   dsd firstobs=2;

input  name $ hire_date company $ country $ date_of_birth;

format hire_date date_of_birth mmddyy10.;
run;

proc print data = ABC;
run;

Now , the code is closer to the correct , leave the name and company variables is not read properly.

Re: how to read the csv file?

SASKiwi — Thu, 20 May 2021 00:39:53 GMT

Please try this. You need INFORMATs for your character variables too as they are longer than 8 characters.

data ABC;
  infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base\practice data\cert\new_hires.csv' dlm = ',' dsd firstobs=2;
  informat name $17. company country $30. hire_date date_of_birth mmddyy10.;
  format hire_date date_of_birth mmddyy10.;
  input  name $ hire_date company $ country $ date_of_birth;
run;

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:39:15 GMT

data ABC;
informat hire_date date_of_birth mmddyy10.;

infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base
\practice data\cert\new_hires.csv'   dsd firstobs=2;

length name $ 16 company $ 25;

input  name $ hire_date company $ country $ date_of_birth;

format hire_date date_of_birth mmddyy10.;
run;

proc print data = ABC;
run;

Perfect .

Re: how to read the csv file?

Reeza — Thu, 20 May 2021 00:40:32 GMT

They never were read properly. What do you think would happen if you also specified the informats for your character variables?

Try it and let us know.

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:45:04 GMT

I don't understand what you mean , but the output of my code (above) is right ,isn't it ?

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:45:48 GMT

That's right, thank you for your help.

Re: how to read the csv file?

Reeza — Thu, 20 May 2021 00:46:40 GMT

What happens if you specify an informat for your character variables instead of using a LENGTH statement.

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:48:00 GMT

wait, the output of your code is wrong .

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 00:51:43 GMT

I need to use length statement ,not use 'informat name $ 16' , the former is correct , and the latter is wrong. I don't know why, just judge it according to the output.

Re: how to read the csv file?

SASKiwi — Thu, 20 May 2021 00:58:46 GMT

Your NAME and COUNTRY variables aren't long enough. Try this:

length name company country $ 25;

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 01:05:24 GMT

You are right. the length is not enough.

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 01:07:32 GMT

the length is not enough, you are right.

data ABC;

infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base
\practice data\cert\new_hires.csv'   dsd firstobs=2;

length name $ 20 company $ 32;

input  name $  hire_date company $ country $  date_of_birth;

informat hire_date date_of_birth mmddyy10.;

format hire_date date_of_birth mmddyy10.;
run;

proc print data = ABC;
run;

now it is correct.

Re: how to read the csv file?

SASKiwi — Thu, 20 May 2021 01:27:52 GMT

You missed making country bigger.

Re: how to read the csv file?

tianerhu — Thu, 20 May 2021 02:29:44 GMT

what I have done ? I am so careless.

Thank you for your remind.

data ABC;

infile 'C:\Users\liaodong\Documents\My SAS Files\prep guide\base
\practice data\cert\new_hires.csv' dsd firstobs=2;

length name $ 20 company $ 32 country $10;

input name $ hire_date company $ country $ date_of_birth;

informat hire_date date_of_birth mmddyy10.;

format hire_date date_of_birth mmddyy10.;
run;

proc print data = ABC;
run;