I want to import csv file and append to SAS data set with changing pattern in file name

[rsingh92]

Hi Folks -  I am new to SAS macros.

My task is to import csv file to SAS data set.  I get new file every morning for example

1. Category_-_Yesterday-11-01-19-0907.csv

2. Category_-_Yesterday-11-02-19-0904.csv

3. Category_-_Yesterday-11-03-19-1016.csv

4. Category_-_Yesterday-11-04-19-0925.csv

5. Category_-_Yesterday-11-05-19-0816.csv

file pattern is like =  Category_-_Yesterday-&today-RandomNumbers

i tried braking this but it doesn't work. It is not able to read RandonNumbers 

%let today=%sysfunc(today(), MMDDYYD8.);
%let file=Category_-_Yesterday-&today-;
%let extension= "*".csv;
%let file_loc=\C:\myfile\&statusfile.&extension.;
%put &today.;
%put &extension.;
%put &file.;
%put &file_loc.;

filename DIRLIST pipe &file_loc ; run;

Can someone please help me on how to read date and random numbers in file name and automate the import process.

Thanks for your help !!

$file

[ChrisNZ]

Do you want to import them all?

Using a data step or proc import?

[rsingh92]

Yes, i want to import them all as well as the new files that i get everyday either way

[ChrisNZ]

> Yes, i want to import them all as well as the new files that i get everyday either way 

How do you which ones to import?

[ChrisNZ]

If you use a data step, try

%let file_name =Category_-_Yesterday-&today-*.csv;
%let file_loc  =C:\myfile\&file_name;

filename DIRLIST "&file_loc" ;

if not, read this:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docse...

[Reeza]

1. Use SAS to get a list of files in the folder. Check the SAS macro appendix for the code on how to do this. 

2. Check a list of files read and if read, leave it, if not read. 
3. read file

4. If successful, add name to dataset of files read. 

Alternatively, you could read all at once every time or just read the newest file. This only works if you only ever update it one file at a time though. Some other tutorials on macros are below. 

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

By the way, those aren’t random numbers at the end, they’re likely the time stamps of when the file was generated to ensure each file generated is uniquely identified.