Importing .csv is filling up the log

[Shaneel]

Hi, 

I am using SAS 9.4 64-bit with Office 16, and I am having an issue when importing a csv file where the log gets filled up. The log is filled with row and row of the below:

I use the following code set-up:

	proc import out= &sas
	            datafile= &file
				dbms= csv replace;
				guessingrows= 100000;
	run;

Removing (and by default reducing) the guessingrows statement means a lot less is printed to the log, but I need the guessingrows set at the 100,000 so that the variables are the right length.

I've tried using options nosource nosource2 nonotes; but that doesn't suppress it. I know I can print to a text file, but I'd rather avoid that as it will fill that log with useless rows as well.

This was not an issue with 32-bit and Office 2010 - the above is from me testing out the 64-bit with Office 16.

Any help will be much appreciated!

Thanks

Shaneel

[Reeza]

When you run the import code you get the generated data step code in the log. Get that data step code, copy from the log, delete line numbers and run it against your data so it's not guessing and it will be infinitely faster. Also, these errors means your input is wrong somehow....it shouldn't fill your log otherwise. To fix the errors, go through the now cleaner list and ensure your variable types and formats are set correctly. 

[Kurt_Bremser]

Why don't you just write the data step code yourself, according to the documentation of the file?

Please post logs or log excerpts by copy/pasting the text into a code box opened with the </> button.

[Kurt_Bremser]

PS if you post the first few lines of the file (also by copy/pasting into a code box after you opened it with a text editor - not Excel!), we can give it a shot.

[Shaneel]

Hi Kurt,

Code:

%macro Import_csv(file, sas);
	proc import out= &sas
	            datafile= &file
				dbms= csv replace;
				guessingrows= 100000;
	run;
%mend;

%Import_csv("&out_fld.\Financial Reporting Database (DUMMY).csv", act);

Dummy dataset below - I've narrowed it down to the fact the log gets filled up where there is a number variable that starts a text string (i.e. the column of "1. XYZ"):

COMPANY,BRANCH,DIVISION,DISTRIBUTION,PORTFOLIO,XCLASS,TCODE,YOA,INC_Y,REF,CCY_REP,FINANCIAL,DESCRIPTION,CURR_QE_REP,PREV_QE_REP,PREV_YE_REP,CCY_MONTH,CCY,FINANCIAL_MARKER,FINANCIAL_WRT,GROSS_MARKER,T3_CODE,GROUP_ORDER,GROUP_NAME,REPORT_ORDER,REPORT_NAME,ITEM_ORDER,ITEM_NAME,USE_PREV,USE_CURR,CCY_TYPE,JNL_ID,JNL_DESC,ACCOUNT_TYPE,ACCOUNT_CODE,ACCOUNT_NAME,SIGNAGE,GROUP,REPORT,ITEM,ACCOUNT,JOURNAL,FX_AVG_EUR,FX_AVG_GBP,FX_AVG_USD,FX_AVG_EUR_USD,FX_CLS_EUR,FX_CLS_GBP,FX_CLS_USD,FX_CLS_EUR_USD,QTD_REP,YTD_REP,QTD_EUR,YTD_EUR,QTD_GBP,YTD_GBP,QTD_USD,YTD_USD,QTD_USD_PRS,YTD_USD_PRS,RUN
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ
XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,XYZ,XYZ,1. XYZ,XYZ,9999,9999,9999,9999,XYZ,9999,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,9999,XYZ,9999,9999,XYZ,XYZ,XYZ,XYZ,9999,XYZ,9999,XYZ,XYZ,XYZ,XYZ,XYZ,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,9999,XYZ

Extract from log:

42802  %Import_csv("&out_fld.\Financial Reporting Database (DUMMY).csv", act);
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary
parameter values will be saved to WORK.PARMS.PARMS.SLIST.
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=
dqstring=1. XYZ
fmt=

As you can see there are a large number of variables, and so have avoided a data step where I would have to define each variable? Also we like the generic use of Import/Export macros that can be used across our project. As I said this had worked on 32-bit with Office 2010 so not sure if there are any changes to 64-bit and Office 16 that would cause this issue...

Thanks

Shaneel

[ballardw]

You might share the DATASTEP code created by the Proc Import.

Your "example" data does not have any column heading dqstring so it would be a significant mystery if the column headings you show are used and Proc Import generates a variable named dqstring to have a problem with.

From the example you show then values of "1. XYZ" would be associated with a variable named FINANCIAL.

And when I copy that example text to a CSV file and import it there are no problems. This is what my proc import generates:

100      data WORK.JUNK    ;
101      %let _EFIERR_ = 0; /* set the ERROR detection macro
101! variable */
102      infile 'C:\users\owner\dummy.csv' delimiter = ','
102! MISSOVER DSD lrecl=32767 firstobs=2 ;
103         informat COMPANY $3. ;
104         informat BRANCH $3. ;
105         informat DIVISION $3. ;
106         informat DISTRIBUTION $3. ;
107         informat PORTFOLIO $3. ;
108         informat XCLASS $3. ;
109         informat TCODE $3. ;
110         informat YOA best32. ;
111         informat INC_Y best32. ;
112         informat REF $3. ;
113         informat CCY_REP $3. ;
114         informat FINANCIAL $6. ;
115         informat DESCRIPTION $3. ;
116         informat CURR_QE_REP best32. ;
117         informat PREV_QE_REP best32. ;
118         informat PREV_YE_REP best32. ;
119         informat CCY_MONTH best32. ;
120         informat CCY $3. ;
121         informat FINANCIAL_MARKER best32. ;
122         informat FINANCIAL_WRT $3. ;
123         informat GROSS_MARKER $3. ;
124         informat T3_CODE $3. ;
125         informat GROUP_ORDER best32. ;
126         informat GROUP_NAME $3. ;
127         informat REPORT_ORDER best32. ;
128         informat REPORT_NAME $3. ;
129         informat ITEM_ORDER best32. ;
130         informat ITEM_NAME $3. ;
131         informat USE_PREV best32. ;
132         informat USE_CURR best32. ;
133         informat CCY_TYPE $3. ;
134         informat JNL_ID $3. ;
135         informat JNL_DESC $3. ;
136         informat ACCOUNT_TYPE $3. ;
137         informat ACCOUNT_CODE best32. ;
138         informat ACCOUNT_NAME $3. ;
139         informat SIGNAGE best32. ;
140         informat GROUP $3. ;
141         informat REPORT $3. ;
142         informat ITEM $3. ;
143         informat ACCOUNT $3. ;
144         informat JOURNAL $3. ;
145         informat FX_AVG_EUR best32. ;
146         informat FX_AVG_GBP best32. ;
147         informat FX_AVG_USD best32. ;
148         informat FX_AVG_EUR_USD best32. ;
149         informat FX_CLS_EUR best32. ;
150         informat FX_CLS_GBP best32. ;
151         informat FX_CLS_USD best32. ;
152         informat FX_CLS_EUR_USD best32. ;
153         informat QTD_REP best32. ;
154         informat YTD_REP best32. ;
155         informat QTD_EUR best32. ;
156         informat YTD_EUR best32. ;
157         informat QTD_GBP best32. ;
158         informat YTD_GBP best32. ;
159         informat QTD_USD best32. ;
160         informat YTD_USD best32. ;
161         informat QTD_USD_PRS best32. ;
162         informat YTD_USD_PRS best32. ;
163         informat RUN $3. ;
164         format COMPANY $3. ;
165         format BRANCH $3. ;
166         format DIVISION $3. ;
167         format DISTRIBUTION $3. ;
168         format PORTFOLIO $3. ;
169         format XCLASS $3. ;
170         format TCODE $3. ;
171         format YOA best12. ;
172         format INC_Y best12. ;
173         format REF $3. ;
174         format CCY_REP $3. ;
175         format FINANCIAL $6. ;
176         format DESCRIPTION $3. ;
177         format CURR_QE_REP best12. ;
178         format PREV_QE_REP best12. ;
179         format PREV_YE_REP best12. ;
180         format CCY_MONTH best12. ;
181         format CCY $3. ;
182         format FINANCIAL_MARKER best12. ;
183         format FINANCIAL_WRT $3. ;
184         format GROSS_MARKER $3. ;
185         format T3_CODE $3. ;
186         format GROUP_ORDER best12. ;
187         format GROUP_NAME $3. ;
188         format REPORT_ORDER best12. ;
189         format REPORT_NAME $3. ;
190         format ITEM_ORDER best12. ;
191         format ITEM_NAME $3. ;
192         format USE_PREV best12. ;
193         format USE_CURR best12. ;
194         format CCY_TYPE $3. ;
195         format JNL_ID $3. ;
196         format JNL_DESC $3. ;
197         format ACCOUNT_TYPE $3. ;
198         format ACCOUNT_CODE best12. ;
199         format ACCOUNT_NAME $3. ;
200         format SIGNAGE best12. ;
201         format GROUP $3. ;
202         format REPORT $3. ;
203         format ITEM $3. ;
204         format ACCOUNT $3. ;
205         format JOURNAL $3. ;
206         format FX_AVG_EUR best12. ;
207         format FX_AVG_GBP best12. ;
208         format FX_AVG_USD best12. ;
209         format FX_AVG_EUR_USD best12. ;
210         format FX_CLS_EUR best12. ;
211         format FX_CLS_GBP best12. ;
212         format FX_CLS_USD best12. ;
213         format FX_CLS_EUR_USD best12. ;
214         format QTD_REP best12. ;
215         format YTD_REP best12. ;
216         format QTD_EUR best12. ;
217         format YTD_EUR best12. ;
218         format QTD_GBP best12. ;
219         format YTD_GBP best12. ;
220         format QTD_USD best12. ;
221         format YTD_USD best12. ;
222         format QTD_USD_PRS best12. ;
223         format YTD_USD_PRS best12. ;
224         format RUN $3. ;
225      input
226                  COMPANY $
227                  BRANCH $
228                  DIVISION $
229                  DISTRIBUTION $
230                  PORTFOLIO $
231                  XCLASS $
232                  TCODE $
233                  YOA
234                  INC_Y
235                  REF $
236                  CCY_REP $
237                  FINANCIAL $
238                  DESCRIPTION $
239                  CURR_QE_REP
240                  PREV_QE_REP
241                  PREV_YE_REP
242                  CCY_MONTH
243                  CCY $
244                  FINANCIAL_MARKER
245                  FINANCIAL_WRT $
246                  GROSS_MARKER $
247                  T3_CODE $
248                  GROUP_ORDER
249                  GROUP_NAME $
250                  REPORT_ORDER
251                  REPORT_NAME $
252                  ITEM_ORDER
253                  ITEM_NAME $
254                  USE_PREV
255                  USE_CURR
256                  CCY_TYPE $
257                  JNL_ID $
258                  JNL_DESC $
259                  ACCOUNT_TYPE $
260                  ACCOUNT_CODE
261                  ACCOUNT_NAME $
262                  SIGNAGE
263                  GROUP $
264                  REPORT $
265                  ITEM $
266                  ACCOUNT $
267                  JOURNAL $
268                  FX_AVG_EUR
269                  FX_AVG_GBP
270                  FX_AVG_USD
271                  FX_AVG_EUR_USD
272                  FX_CLS_EUR
273                  FX_CLS_GBP
274                  FX_CLS_USD
275                  FX_CLS_EUR_USD
276                  QTD_REP
277                  YTD_REP
278                  QTD_EUR
279                  YTD_EUR
280                  QTD_GBP
281                  YTD_GBP
282                  QTD_USD
283                  YTD_USD
284                  QTD_USD_PRS
285                  YTD_USD_PRS
286                  RUN $
287      ;
288      if _ERROR_ then call symputx('_EFIERR_',1);  /* set
288! ERROR detection macro variable */
289      run;

NOTE: The infile 'C:\users\owner\dummy.csv' is:
      Filename=C:\users\owner\dummy.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=3948,
      Last Modified=02Sep2020:15:15:55,
      Create Time=02Sep2020:15:15:55

NOTE: 12 records were read from the infile
      'C:\users\owner\dummy.csv'.
      The minimum record length was 278.
      The maximum record length was 278.
NOTE: The data set WORK.JUNK has 12 observations and 61
      variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

One thing that might be happening is that what has been pasted here as a space in the "1. XYZ" value is some other character that doesn't display well and is causing a problem.

[Shaneel]

Hi all, thanks for the help so far, but having been in contact with SAS technical support they have said the following:

I was able to replicate the problem running in SAS 9.4M7. This does not occur in SAS 9.4M6.  This is a known regression and development will address it in a future release.

So unfortunately nothing to be done until that release. I will likely just print the log to a text file for the import step, then revert back.

Thanks

Shaneel

[Kurt_Bremser]

The question remains: is it a problem of IMPORT, or does it occur when you write the DATA step yourself?

A defective DATA step is serious, a problem with IMPORT for a CSV is next to a non-issue, as it is simply not needed in a professional setting.