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?
@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.
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.
%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 |
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.