topic Reading csv filename as a variable column in the dataset in SAS Programming

Reading csv filename as a variable column in the dataset

vipul257 — Tue, 23 Jan 2018 04:49:17 GMT

Hi All,

I am trying to use the INFILE process to read a csv file.

However, the CSV file name contains a date (YYYYMMDD format) followed by a string which I need to capture as additional columns in the dataset that is created by the INFILE statement.

The resulting dataset I am looking for is:

Columns A - X: contents of the CSV file

Column Y: Date from the filename

Column Z: Part of a string from the filename.

it would be great if I can read multiple CSV files at once and read the date and string from each file name and append to the columns in the dataset.

Thanks for your help.

My code to simple read the file is:

DATA WORK.ALL1;

LENGTH

SOURCE_Name_1 $ 10

SOURCE_Name_2 $ 50;

FORMAT

SOURCE_Name_1 $CHAR10.

SOURCE_Name_2 $CHAR50.;

INFORMAT

SOURCE_Name_1 $CHAR10.

SOURCE_Name_2 $CHAR50.;

INFILE '\\filepath\*.csv'

LRECL=32767

DLM=','

DSD

FIRSTOBS=2

MISSOVER;

INPUT

SOURCE_Name_1 : $CHAR10.

SOURCE_Name_2 : $CHAR50.;

RUN;

Re: Reading csv filename as a variable column in the dataset

Shmuel — Tue, 23 Jan 2018 06:55:25 GMT

Use the FILENAME= option in the INFILE statement to get the current csv input file

length csv_name _filepath $200;
infile '//filepath/*.csv'   filename = _filepath ... ;

csv_name = _filename;

then extract the date and required column using SUBSTR function.

Re: Reading csv filename as a variable column in the dataset

Kurt_Bremser — Tue, 23 Jan 2018 07:02:50 GMT

Use the filename= option in the infile statement:

data work.all1;
length infilename $200;
infile "&filepath.\*.csv"
  lrecl=32767
  dlm=','
  dsd
  firstobs=2
  truncover
  filename=infilename
;

Now the variable infilename will hold the name of the current infile.

Be aware that the firstobs= option works only on the first file, header lines in the succeeding files need to be filtered in the step.

Edit: corrected filevar= to filename=