topic Re: Import csv files from a directory - based on date range in SAS Procedures

Import csv files from a directory - based on date range

Q1983 — Tue, 28 Jan 2020 21:27:35 GMT

data _null_

call symput('rpt_date',put(today(),YYMMDDn8.));

run;

%let mydir=/myshare/myfolder/Suspense_Detail;

data mps1;

infile "cd &mydir ; ls *.csv" pipe truncover;

input filename $256. ;

run;

proc sort data=mps1;

by filename;

run;

data datasets;

fileno+1;

set mps1;

length memname $32. ;

memname = cats('csv',fileno);

call execute(catx(' '

,'proc import datafile='

,quote(catx('/','&mydir',filename))

,'out=',memname,'replace'

,'dbms=csv'

,';run;'

));

run;

I successfully imported about 300 separate files into sas.

In the actual myfolder directory the naming convention is

sm_work_list&rpt_date..csv

Results are

sm_work_list_20200129.csv

sm_work_list_20200128.csv

sm_work_list_20200127.csv etc. There are 300 in all

I use a data null to capture the various dates as you can see above

Questions

1. Is there a way I can limit the pull to just the last 30 days based on the data_null I want to just pull the last 30 days worth or based on the number of days in the month. So lets say today is 1/15/20 I would need just 15 days worth of csv files.

2. I would then want to combine them into one dataset. Currently they result in separate datasets numbered.

Re: Import csv files from a directory - based on date range

Reeza — Tue, 28 Jan 2020 22:01:13 GMT

1. Filter your mps1 data set. You can use the data step itself to apply the filter. The import only runs for files in that list.

2. How are they to be combined? Are all the files the same so you're stacking them or do they need to be merged somehow?

If the files are all the same format and need to be stacked, you're better off reading the files within a single data step that can read all the files together at once based off your filtered mps1 dataset.

Re: Import csv files from a directory - based on date range

Q1983 — Tue, 28 Jan 2020 22:20:50 GMT

1. How would I filter. Can I get a code example. Would it be done here

data mps1;

infile "cd &mydir ; ls *.csv" pipe truncover;

input filename $256. ;

run;

2. The fields are exactly the same just the data is different based on the date. Essentially it needs to be stacked.

Re: Import csv files from a directory - based on date range

Reeza — Tue, 28 Jan 2020 22:30:16 GMT

1. grab the date from the variable value

2. filter that based on 15 days using an IF statement.

I can't provide example code because I'm not sure what your input looks like, there was no sample data provided that I noticed.

Here's an example of using wildcards to read in all text files at once. You can use FILEVAR instead to pass your filename variable and import just those files. The documentation on the INFILE statement has some examples.

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

@Q1983 wrote:

1. How would I filter. Can I get a code example. Would it be done here

data mps1;

infile "cd &mydir ; ls *.csv" pipe truncover;

input filename $256. ;

run;

2. The fields are exactly the same just the data is different based on the date. Essentially it needs to be stacked.