DATA Step, Macro, Functions and more

Reading a .csv file into SAS with double quotes

Accepted Solution Solved
Reply
Super Contributor
Posts: 287
Accepted Solution

Reading a .csv file into SAS with double quotes

[ Edited ]

Code I have:

data WORK.ONE ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile "G:\Departments\Research\test scores\GT\&yr.\GIFT.csv" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

 

A few vars have double quotes surrounding them causing that obs to read in incorrectly. How can I edit the infile statement?


Accepted Solutions
Solution
‎12-19-2017 10:55 AM
Super User
Posts: 24,026

Re: Reading a .csv file into SAS with double quotes


GreggB wrote:

 

 

A few vars have double quotes surrounding them causing that obs to read in incorrectly. How can I edit the infile statement?


That's correct in a CSV and DSD knows that you should interpret that as text. 

Check your format/informat are correct for the variables in question.

 

Since you used PROC IMPORT to generate the code, try setting GUESSINGROWS=MAX in the code and see if that helps. It'll take longer to run the first time.

View solution in original post


All Replies
Super User
Posts: 13,942

Re: Reading a .csv file into SAS with double quotes

Show your entire code and some example of the problem data.

I suspect the issue will be the informat or input statement , since the very partial code you show was almost certainly generated by Proc import (%let _EFIERR_ = 0; /* set the ERROR detection macro variable */ is inserted by Proc Import).

 

If you ran the proc import to generate the code you may want to go back and make sure that the GUESSINGROWS option was set to a large value.

 

Quotes in the body of a CSV file are necessary when there are commas in the data text so that the comma in the body of the field does not trigger an "end of variable condition" that a comma normally does.

 

Super Contributor
Posts: 287

Re: Reading a .csv file into SAS with double quotes

%let yr=1718;
%inc 'H:\Macros\my_macros.sas';
proc import datafile="G:\Departments\Research\test scores\GT\&yr.\GIFT.csv"
out=one
dbms=dlm replace;
getnames=yes;
delimiter=',';
run;
proc print data=one (obs=5);
run;
data WORK.ONE ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile "G:\Departments\Research\test scores\GT\&yr.\GIFT.csv" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat dob mmddyy10.;
format gift_date cutoffdate $10. sasi_id best12. id10 best12. stud_id best12. lname $18. fname $22. guardians $37. address $27. city $13.
state $4. zip $7. l_update $10. phone $14. sex $3. race $3. lunch $3. grade $4. lep $3. iep $1. sch_code $11.
school $23. lschcode $2. nschcode $5. teacher $18. del_d $4. edate $4. e1date $4. e2date $4. duprec $3. check1 $1. flag $1. gtflag $2.
refrd $1. refrd_d $4. refpmr $2. refnam $2. cogat_t best12. cogat_v best12. cogat_nv best12. cogat_q best12. cogat_g best12.
cogat_d $10. cogatb_t $2. cogatb_v cogatb_nv cogatb_q cogatb_g $2. cogatb_d $10. olsat_tot $2.
olsat_v $2. olsat_nv best12. olsat_g best12. olsat_d $4. olsatb_tot $2. olsatb_v $2. olsatb_nv best12. olsatb_g best12.
olsatb_d $4. tcs_tot $2. tcs_v $2. tcs_nv best12. tcs_g best12. tcs_d $4. tcsb_tot $2. tcsb_v $2. tcsb_nv best12.
tcsb_g best12. tcsb_d $4. raven $2. raven_g best12. raven_d $4. raven1 $2. raven1_g best12. raven1_d $4. o_apt_name $2.
o_apt_t best12. o_apt_v best12. o_apt_m best12. o_apt_q best12. o_apt_g best12. o_apt_d $4. o_apt1_nam $2. o_apt1_t best12.
o_apt1_v best12. o_apt1_m best12. o_apt1_q best12. o_apt1_g best12. o_apt1_d $4. itbs_r $2. itbs_m $2. itbs_g best12. itbs_d $10.
mat7_rc $2. mat7_mc best12. mat7_g best12. mat7_d $4. tn_tr $2. tn_tm best12. tn_g best12. tn_d $4. mapfr $2.
mapfrrit best12. mapfrlxl best12. mapfm best12. mapfmrit best12. mapfg best12. mapfd $10. mapwr $2. mapwrrit best12. mapwrlxl best12.
mapwm best12. mapwmrit best12. mapwg best12. mapwd $10. mapsr $2. mapsrrit best12. mapsrlxl best12. mapsm best12. mapsmrit best12.
mapsg best12. mapsd $10. scr_ela $4. scr_math $4. scr_g $3. scr_d $10. pass_ela $3. pass_math $4. pass_g $3. pass_d $10.
actrss $3. actmss best12. actg $3. actd $10. o_ach_name $2. o_ach_v best12. o_ach_m best12. o_ach_g best12. o_ach_d $4.
o_ach1_nam $2. o_ach1_v best12. o_ach1_m best12. o_ach1_g best12. o_ach1_d $4. pd_gr $6. pd_g $6. pd_gr_d $10. ptp_v $2.
ptp_nv $2. ptp_g best12. ptp_d $10. pti_v $2. pti_nv best12. pti_g best12. pti_d $10. apt_att $1.
gtstatus $15. ngt $2. gtserve $5. aform $4. gtmodel $3. cm $1. cm1 $2. cm2 $2. cm3 $2. cm4 $2.
cm5 $2. reviewteam $2. daname $7. datot $2. dav best12. danv best12. daq best12. dag best12. dad $10. dbname $6. dbe $2.
dbr best12. dbm best12. dbg best12. dbd $10. dcname $2. dcv $2. dcnv best12. dcgpa $2. dcg best12. dcd $4. artstatus $2.
artdanrfby $2. artdanname $2. artrfdan $4. artdanscr1 $2. artdanscr2 $2. artdanscr3 $2. artscdan $4. artdanstat $12. artdanmodl $5. artnedan $4.
artdrarfby $2. artdraname $2. artrfdra $4. artdrascr1 $2. artdrascr2 $2. artdrascr3 $2. artscdra $4. artdrastat $12. artdramodl $5. artnedra $4.
artmusrfby $2. artmusname $2. artrfmus $4. artmusscr1 $2. artmusscr2 $2. artmusscr3 $2. artscmus $4. artmusstat $12. artmusmodl $5. artnemus $4.
artvarfby $2. artvaname $2. artrfva $4. artvascr1 $2. artvascr2 $2. artvascr3 $2. artscva $4. artvastat $12. artvamodl $5. artneva $4.
achhistory $2. extran1 best12. extran2 best12. extrac1 $2. extrac2 $2. extrac3 $2. ;
input sasi_id id10 stud_id stateid lname $ fname $ guardians $ address $ city $ state $ zip $ l_update $ phone $ dob sex $ race $ lunch $ grade $
lep $ iep $ sch_code $ school $ lschcode $ nschcode $ teacher $ del_d $ edate $ e1date $ e2date $ duprec $ check1 $ flag $ gtflag $
refrd $ refrd_d $ refpmr $ refnam $ cogat_t cogat_v cogat_nv cogat_q cogat_g cogat_d $ cogatb_t $ cogatb_v $ cogatb_nv $ cogatb_q $ cogatb_g $
cogatb_d $ olsat_tot $ olsat_v $ olsat_nv olsat_g olsat_d $ olsatb_tot $ olsatb_v $ olsatb_nv olsatb_g olsatb_d $ tcs_tot $ tcs_v $ tcs_nv tcs_g
tcs_d $ tcsb_tot $ tcsb_v $ tcsb_nv tcsb_g tcsb_d $ raven $ raven_g raven_d $ raven1 $ raven1_g raven1_d $ o_apt_name $ o_apt_t o_apt_v o_apt_m
o_apt_q o_apt_g o_apt_d $ o_apt1_nam $ o_apt1_t o_apt1_v o_apt1_m o_apt1_q o_apt1_g o_apt1_d $ itbs_r $ itbs_m $ itbs_g itbs_d $
mat7_rc mat7_mc mat7_g mat7_d $ tn_tr $ tn_tm tn_g tn_d $ mapfr mapfrrit mapfrlxl mapfm mapfmrit mapfg mapfd $ mapwr $ mapwrrit
mapwrlxl mapwm mapwmrit mapwg mapwd $ mapsr $ mapsrrit mapsrlxl mapsm mapsmrit mapsg mapsd $ scr_ela $ scr_math $ scr_g $ scr_d $ pass_ela $
pass_math $ pass_g $ pass_d $ actrss $ actmss actg $ actd $ o_ach_name $ o_ach_v o_ach_m o_ach_g o_ach_d $ o_ach1_nam $ o_ach1_v o_ach1_m
o_ach1_g o_ach1_d $ pd_gr $ pd_g $ pd_gr_d $ ptp_v $ ptp_nv $ ptp_g ptp_d $ pti_v $ pti_nv pti_g pti_d $ cutoffdate $ apt_att $ gtstatus $ ngt $
gtserve $ gift_date $ aform $ gtmodel $ cm $ cm1 $ cm2 $ cm3 $ cm4 $ cm5 $ reviewteam $ daname $ datot $ dav danv daq dag dad $
dbname $ dbe $ dbr dbm dbg dbd $ dcname $ dcv $ dcnv dcgpa $ dcg dcd $ artstatus $ artdanrfby $ artdanname $ artrfdan $ artdanscr1 $
artdanscr2 $ artdanscr3 $ artscdan $ artdanstat $ artdanmodl $ artnedan $ artdrarfby $ artdraname $ artrfdra $ artdrascr1 $ artdrascr2 $
artdrascr3 $ artscdra $ artdrastat $ artdramodl $ artnedra $ artmusrfby $ artmusname $ artrfmus $ artmusscr1 $ artmusscr2 $ artmusscr3 $
artscmus $ artmusstat $ artmusmodl $ artnemus $ artvarfby $ artvaname $ artrfva $ artvascr1 $ artvascr2 $ artvascr3 $ artscva $ artvastat $ artvamodl $
artneva $ achhistory $ extran1 extran2 extrac1 $ extrac2 $ extrac3 $ ;
run;

 

Problem Obs: (personal identifying data changed by me)

SAS Output

Obs school dob gift_date cutoffdate sasi_id id10 stud_id lname fname guardians address city state zip l_update phone sex race lunch grade lep iep sch_code lschcode nschcode teacher del_d edate e1date e2date duprec check1 flag gtflag refrd refrd_d refpmr refnam cogat_t cogat_v cogat_nv cogat_q cogat_g cogat_d cogatb_t cogatb_v cogatb_nv cogatb_q cogatb_g cogatb_d olsat_tot olsat_v olsat_nv olsat_g olsat_d olsatb_tot olsatb_v olsatb_nv olsatb_g olsatb_d tcs_tot tcs_v tcs_nv tcs_g tcs_d tcsb_tot tcsb_v tcsb_nv tcsb_g tcsb_d raven raven_g raven_d raven1 raven1_g raven1_d o_apt_name o_apt_t o_apt_v o_apt_m o_apt_q o_apt_g o_apt_d o_apt1_nam o_apt1_t o_apt1_v o_apt1_m o_apt1_q o_apt1_g o_apt1_d itbs_r itbs_m itbs_g itbs_d mat7_rc mat7_mc mat7_g mat7_d tn_tr tn_tm tn_g tn_d mapfr mapfrrit mapfrlxl mapfm mapfmrit mapfg mapfd mapwr mapwrrit mapwrlxl mapwm mapwmrit mapwg mapwd mapsr mapsrrit mapsrlxl mapsm mapsmrit mapsg mapsd scr_ela scr_math scr_g scr_d pass_ela pass_math pass_g pass_d actrss actmss actg actd o_ach_name o_ach_v o_ach_m o_ach_g o_ach_d o_ach1_nam o_ach1_v o_ach1_m o_ach1_g o_ach1_d pd_gr pd_g pd_gr_d ptp_v ptp_nv ptp_g ptp_d pti_v pti_nv pti_g pti_d apt_att gtstatus ngt gtserve aform gtmodel cm cm1 cm2 cm3 cm4 cm5 reviewteam daname datot dav danv daq dag dad dbname dbe dbr dbm dbg dbd dcname dcv dcnv dcgpa dcg dcd artstatus artdanrfby artdanname artrfdan artdanscr1 artdanscr2 artdanscr3 artscdan artdanstat artdanmodl artnedan artdrarfby artdraname artrfdra artdrascr1 artdrascr2 artdrascr3 artscdra artdrastat artdramodl artnedra artmusrfby artmusname artrfmus artmusscr1 artmusscr2 artmusscr3 artscmus artmusstat artmusmodl artnemus artvarfby artvaname artrfva artvascr1 artvascr2 artvascr3 artscva artvastat artvamodl artneva achhistory extran1 extran2 extrac1 extrac2 extrac3 stateid
5043 East High 20982   0 191 191 0 Smith Connor Pete "Maker Kyndle or Maker Hope "Ch" 136 Fair Pl TX 00643 555 12/ M W F 9 9 00 Left 30-01-000 8 Wats / / / / / /   T F   F / . . 0 0 0 0 0 / 0 0 0 0 0 / 0 0 0 0 / 0 0 0 0 / 0 0 0 0 / 0 0 0 0 . 0 0 . 0 0 . . 0 0 0 0 0 . . 0 0 0 0 0 / 0 0 0 . 0 0 0 . 0 0 0 . 93 240 0 47 225 8 . 0 0 0 0 0 0 . 91 241 0 58 235 8 04/1 743 583 8 05/0 0 0   . 0 0   . . 0 0 0 . . 0 0 0 / /   0 / 0 0 0 . 0 0 / 06/14/2000 0 No N/A 12/ 0   F             . 0 0 0 0 0 / . 0 0 0 0 / . 0 0   0 /       /       / / Inel N/ /     /       / / Inel N/ /     /       / / Inel N/ /     /       / / Inel N/ . . 0 0   3003484000

 

Solution
‎12-19-2017 10:55 AM
Super User
Posts: 24,026

Re: Reading a .csv file into SAS with double quotes


GreggB wrote:

 

 

A few vars have double quotes surrounding them causing that obs to read in incorrectly. How can I edit the infile statement?


That's correct in a CSV and DSD knows that you should interpret that as text. 

Check your format/informat are correct for the variables in question.

 

Since you used PROC IMPORT to generate the code, try setting GUESSINGROWS=MAX in the code and see if that helps. It'll take longer to run the first time.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 935 views
  • 0 likes
  • 3 in conversation