I have a csv file (that's not really a true csv file) that I'm trying to import and am wondering if there is a way to only import x number of rows until it reads the "total" line. The reason being, my PROC IMPORT is erroring out when it hits the "Total" value, as it's not a date value (which makes sense). The number of rows varies on a weekly basis, so I can't set it based on x number of rows. However, I'm hoping I can set a condition in the import statement to only read in the data until it get's to the "total" line and then ignore anything after the last date. I'll provide an example table below:
Date | Make | COUNT |
JAN2019 | TOYOTA | 3 |
FEB2019 | FORD | 17 |
MAR2019 | CHEVY | 5 |
APR2019 | MAZDA | 5 |
TOTAL | 30 | |
RANGE JAN2019 TO APR2019 |
I'm not sure if a WHERE statement could work, but am open to any and all suggestions.
Pseudo code of similar process:
data read; infile "<path>\somefile.csv" dlm=',' dsd lrecl=32000;
<here should go informat settings for the variables you want
you should be able to get them from one your prod IMPORT
steps as the proc writes data step code to the log
that it generates> input @; if _infile_ =: "Total" then do; <do nothing>
input; end; else do;
input date make count;
end;
; run;
A) do NOT use PROC IMPORT
B) in the DATA step, first do a "blind read" (input@;), then check if the word "TOTAL" appears in _infile_, and if not proceed to read from @1.
If that is not clear enough, please post your data step code, and I will show you the necessary modification.
Hi @BlayLay ,
Instead of using proc import, you could try using a data step so that you could examine the text in your input file by reading the first column as character and if it is a date then apply an informat to convert it to a SAS date.
There are some examples of reading a csv in:
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=n...
Kind regards,
Amir.
Pseudo code of similar process:
data read; infile "<path>\somefile.csv" dlm=',' dsd lrecl=32000;
<here should go informat settings for the variables you want
you should be able to get them from one your prod IMPORT
steps as the proc writes data step code to the log
that it generates> input @; if _infile_ =: "Total" then do; <do nothing>
input; end; else do;
input date make count;
end;
; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.