Hello, I used http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docset... 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?
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;
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.
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;
That is exactly what I need. Thank you Tom!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.