Reading a .csv file into SAS with double quotes

GreggB — Tue, 19 Dec 2017 15:07:51 GMT

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?

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

ballardw — Tue, 19 Dec 2017 15:38:46 GMT

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.

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

GreggB — Tue, 19 Dec 2017 15:51:37 GMT

%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

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

Reeza — Tue, 19 Dec 2017 15:53:54 GMT

@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.

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

Reading a .csv file into SAS with double quotes

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

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

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