topic Re: Conditional Import based on row value in SAS Programming

Conditional Import based on row value

BlayLay — Thu, 29 Oct 2020 12:49:00 GMT

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.

Re: Conditional Import based on row value

Kurt_Bremser — Thu, 29 Oct 2020 13:24:14 GMT

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.

Re: Conditional Import based on row value

Amir — Thu, 29 Oct 2020 13:26:48 GMT

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=n1rill4udj0tfun1fvce3j401plo.htm&locale=en

Kind regards,

Amir.

Re: Conditional Import based on row value

ballardw — Thu, 29 Oct 2020 14:32:13 GMT

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;