Syntax Error for proc import?

[ybz12003]

Hello:

I found syntax error was shown when I use proc import.   I think it's because of double * in the dataline.   Please advise how to fix it.  Thanks.

%let State=CA;

%let Year=2009;

PROC IMPORT OUT=Education&state&year

DATAFILE= "\\abc.org\project\Share\ACS\&state\&year\Education&year..csv"

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

183 PROC IMPORT OUT=Education&state.&year

SYMBOLGEN: Macro variable STATE resolves to CA

SYMBOLGEN: Macro variable YEAR resolves to 2009

184 DATAFILE= "\\abc.org\project\Share\ACS

184! data\&state\&year\Education&year..csv"

SYMBOLGEN: Macro variable STATE resolves to CA

SYMBOLGEN: Macro variable YEAR resolves to 2009

SYMBOLGEN: Macro variable YEAR resolves to 2009

185 DBMS=CSV REPLACE;

186 GETNAMES=YES;

187 DATAROW=2;

188 RUN;

189 /**********************************************************************

190 * PRODUCT: SAS

191 * VERSION: 9.4

192 * CREATOR: External File Interface

193 * DATE: 16OCT17

194 * DESC: Generated SAS Datastep Code

195 * TEMPLATE SOURCE: (None Specified.)

196 ***********************************************************************/

197 data WORK.EDUCATIONCA2009 ;

198 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

199 infile '\\abc.org\project\Share\ACS

199! data\CA\2009\Education2009.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

200 informat GEO_id $20. ;

201 informat GEO_id2 best32. ;

202 informat GEO_display_label $47. ;

203 informat HC01_EST_VC06 best32. ;

204 informat HC01_MOE_VC06 best32. ;

205 informat HC02_EST_VC06 best32. ;

206 informat HC02_MOE_VC06 best32. ;

207 informat HC03_EST_VC06 best32. ;

208 informat HC03_MOE_VC06 best32. ;

209 informat HC01_EST_VC07 best32. ;

210 informat HC01_MOE_VC07 best32. ;

211 informat HC02_EST_VC07 best32. ;

.

.

.

233 format GEO_id $20. ;

234 format GEO_id2 best12. ;

235 format GEO_display_label $47. ;

236 format HC01_EST_VC06 best12. ;

237 format HC01_MOE_VC06 best12. ;

238 format HC02_EST_VC06 best12. ;

239 format HC02_MOE_VC06 best12. ;

240 format HC03_EST_VC06 best12. ;

241 format HC03_MOE_VC06 best12. ;

242 format HC01_EST_VC07 best12. ;

243 format HC01_MOE_VC07 best12. ;

244 format HC02_EST_VC07 best12. ;

245 format HC02_MOE_VC07 best12. ;

233 format GEO_id $20. ;

234 format GEO_id2 best12. ;

235 format GEO_display_label $47. ;

236 format HC01_EST_VC06 best12. ;

237 format HC01_MOE_VC06 best12. ;

238 format HC02_EST_VC06 best12. ;

239 format HC02_MOE_VC06 best12. ;

240 format HC03_EST_VC06 best12. ;

241 format HC03_MOE_VC06 best12. ;

242 format HC01_EST_VC07 best12. ;

243 format HC01_MOE_VC07 best12. ;

244 format HC02_EST_VC07 best12. ;

245 format HC02_MOE_VC07 best12. ;

.

.

.

300 ;

301 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

302 run;

NOTE: Invalid data for HC03_EST_VC07 in line 923 121-121.

NOTE: Invalid data for HC03_MOE_VC07 in line 923 123-124.

NOTE: Invalid data for HC03_EST_VC08 in line 923 144-144.

NOTE: Invalid data for HC03_MOE_VC08 in line 923 146-147.

NOTE: Invalid data for HC03_EST_VC12 in line 923 165-165.

NOTE: Invalid data for HC03_MOE_VC12 in line 923 167-168.

NOTE: Invalid data for HC03_EST_VC13 in line 923 186-186.

NOTE: Invalid data for HC03_MOE_VC13 in line 923 188-189.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---

923 1400000US06029004302,6029004302,"Census Tract 43.02, Kern County, California",4476,277,4

89 476,277,0,132,10.6,2.5,10.6,2.5,-,**,36.3,4.8,36.3,4.8,-,**,2.1,1.3,2.1,1.3,-,**,0.8,0.9

177 ,0.8,0.9,-,** 189

GEO_id=1400000US06029004302 GEO_id2=6029004302

GEO_display_label=Census Tract 43.02, Kern County, California HC01_EST_VC06=4476 HC01_MOE_VC06=277

HC02_EST_VC06=4476 HC02_MOE_VC06=277 HC03_EST_VC06=0 HC03_MOE_VC06=132 HC01_EST_VC07=10.6

HC01_MOE_VC07=2.5 HC02_EST_VC07=10.6 HC02_MOE_VC07=2.5 HC03_EST_VC07=. HC03_MOE_VC07=.

HC01_EST_VC08=36.3 HC01_MOE_VC08=4.8 HC02_EST_VC08=36.3 HC02_MOE_VC08=4.8 HC03_EST_VC08=.

HC03_MOE_VC08=. HC01_EST_VC12=2.1 HC01_MOE_VC12=1.3 HC02_EST_VC12=2.1 HC02_MOE_VC12=1.3

HC03_EST_VC12=. HC03_MOE_VC12=. HC01_EST_VC13=0.8 HC01_MOE_VC13=0.9 HC02_EST_VC13=0.8

HC02_MOE_VC13=0.9 HC03_EST_VC13=. HC03_MOE_VC13=. _ERROR_=1 _N_=922

.

.

.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

ERROR: Import unsuccessful. See SAS Log for details.

[RW9 · Accepted solution]

Yes, the proc import procedure is a guessing procedure, so it analyses the data and guesses what the column's type should be, in this case it decides that variable is numeric, and then it hits a data item - ** which is not numeric and not convertible, so throws and error.  I suppose first question would be why you have ** in a numeric column - if you want to keep those then make that column character.  As for the import, I would only use proc import once, take the code it generates and then modify it to correctly match the data format which you know best, i.e. avoid the guessing.  In this case changing that column to a text one should import all the data, however if you want numeric, then either removes these ** items, or read in as character and then create a new numeric variable from the character data.

[Kurt_Bremser]

This is not a syntax error, as the SAS code is valid. This is a data error, detected because proc import writes code that checks the automatic data step variable _ERROR_ and sets a macro variable if data errors are found:

301 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

[ybz12003]

Actually, the proc import program was continue.  SAS just automatically assign the dot to replace the '_' and '**'. 

[Reeza]

On the PROC IMPORT set GUESSINGROWS=MAX so that SAS scans more of the file before it makes the type/format 'guess'. 

[ballardw]

Did your data source provide a key for why it contains ** for some variables that are generally numeric? I would expect a document from the source says something like "Values that cannot be reliably estimated are shown as ** in the data sets." or similar.

For example http://www2.census.gov/programs-surveys/acs/tech_docs/data_suppression/ACSO_Data_Suppression.pdf is a document for why certain values from the American Community Survey suppresses data in some tables.

You don't specify that you are working with ACS data but your path name and example data is suggestive.

Likely you will want the ** to be missing for final analysis but documentation may provide more details that you can include in your analysis or final report.

[ybz12003]

Thanks, Ballardw.   The file is very helpful information.