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

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

rsingh92 — Thu, 21 Nov 2019 22:10:12 GMT

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

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

ChrisNZ — Thu, 21 Nov 2019 22:28:35 GMT

Do you want to import them all?

Using a data step or proc import?

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

rsingh92 — Thu, 21 Nov 2019 22:33:19 GMT

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

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

ChrisNZ — Thu, 21 Nov 2019 22:34:57 GMT

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&docsetVersion=9.4

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

ChrisNZ — Thu, 21 Nov 2019 22:35:57 GMT

> 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?

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

Reeza — Fri, 22 Nov 2019 02:00:29 GMT

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-Appendix/ta-p/291716

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.