topic Re: "Example 1: Import All CSV Files That Exist within a Directory" - but I need them proc in SAS Enterprise Guide

"Example 1: Import All CSV Files That Exist within a Directory" - but I need them processed in order

clocks212 — Wed, 24 Jul 2019 17:41:33 GMT

Hello, I used http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en to set up my macro and it will happily chug along through my directory full of csvs.

Unfortunately the macro seems to be processing the files in a random order. My csv files overlap in date ranges and I need to make sure they process in order so my code handles overwriting data from older files correctly.

The file names in my directory start with values which increase (but dont increment by any specific amount), with the oldest files having the lowest value as the first six digits:

138281_longfilename.csv

142049_longfilename.csv

192820_longfilename.csv

Any suggestions on how to tell the macro to sort the files in the directory by the first 6 digits?

Re: "Example 1: Import All CSV Files That Exist within a Directory" - but I need them proc

Reeza — Wed, 24 Jul 2019 17:45:17 GMT

This particular example only imports each data individually, and creates a unique data set for each file. Not sure how that affects anything in order, the SAS data sets will be each unique to the file and you can then process them further in whatever order you like. Add a step that adds the filename to the data for each data set or to the dataset name if you need that information.

Re: "Example 1: Import All CSV Files That Exist within a Directory" - but I need them proc

clocks212 — Wed, 24 Jul 2019 17:49:37 GMT

I should have added that I replaced this line in the example...

proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
           dbms=csv replace;

...with the big block of import and de-dup code which I used to process one file at a time. So the "Example 1..." code no longer imports each file as its own data set but instead imports the file, does some proc sql work comparing, deduping, and union'ing it with my master table.

Re: "Example 1: Import All CSV Files That Exist within a Directory" - but I need them proc

Reeza — Wed, 24 Jul 2019 17:50:10 GMT

I can only see what you've shown and can only respond accordingly.

Re: "Example 1: Import All CSV Files That Exist within a Directory" - but I need them proc

Tom — Wed, 24 Jul 2019 17:53:39 GMT

Make the list of names as a dataset. If you are using Windows instead of Unix then adjust the command used to get the list of files appropriately. If you cannot use PIPE then use a data step that uses the method in your posted link. No need to do it in macro code instead of a data step since your macro is already generating other SAS code.

%let mydir=/folder/where/my/csv/files/live;

data files;
  infile "cd &mydir ; ls *.csv" pipe truncover;
  input filename $256. ;
run;

then sort the list into the order you want to process them.

proc sort data=files;
  by filename;
run;

Then use the list to generate code

data datasets;
  fileno+1;
  set files;
  length memname $32. ;
  memname = cats('csv',fileno);
  call execute(catx(' '
       ,'proc import datafile='
       ,quote(catx('/','&mydir',filename))
       ,'out=',memname,'replace'
       ,'dbms=csv'
       ,';run;'
  ));
run;

Re: "Example 1: Import All CSV Files That Exist within a Directory" - but I need them proc

clocks212 — Wed, 24 Jul 2019 17:53:27 GMT

That is exactly what I need. Thank you Tom!