Hi,
I'm really new to SAS and i am required to create a dataset that appends a csv file daily.
The csv file is dropped onto a location daily with the following naming convention: sample_set_20191114.csv
Is there a way for me to run through the directory daily and automatically pick up and append the latest file to my 'sample' dataset using the date in the file name? or any other way to achieve this.
All formats are exactly the same.
Any reading material that could assist with this would be appreciated.
I have created the following example of a dataset:
data sample;
infile '\\mylocation\sample.csv'
delimiter=','
missover
firstobs=2
DSD
lrecl = 32767;
format make $7. ;
format model $15. ;
input
make $
model $
;
run;
I @sophie07 Taking into account, your daily files have a date suffix formatted as yymmddn8.,A way to approach this by dynamic date macro.
For example
228 %let today=%sysfunc(today(),yymmddn8.);
229
230 %put &=today;
TODAY=20191114
You can add the &today suffix in your infile statement placeholder. So when the code executes on a daily basis, your macro variable will have that corresponding date
I @sophie07 Taking into account, your daily files have a date suffix formatted as yymmddn8.,A way to approach this by dynamic date macro.
For example
228 %let today=%sysfunc(today(),yymmddn8.);
229
230 %put &=today;
TODAY=20191114
You can add the &today suffix in your infile statement placeholder. So when the code executes on a daily basis, your macro variable will have that corresponding date
Hi!
thanks so much for the response.
could you explain how i do the append step?
sorry if this question doesnt make sense...this is probably my second time using SAS.
or direct me to a forum that explains this would be great as well 🙂
I am assuming you would have done some online search on how to append SAS datasets. You could read about Proc append, using SET statements to append and so on. Since .csv is an external file, your 1st step is to convert the csv file to a SAS dataset or in other read into a SAS dataset form which you seem to have started with Infile and input. Then, once you have .csv in the form of a SAS dataset, you could use this dataset in the procedures mentioned to append.
The macro variable suggestion was just to make the process dynamic. Nonetheless, what you probably need is some reading and search online.
i tried the &=today suffix in my infile step however, i get an error saying the physical file does not exist.
not sure if i am doing something wrong.
Use &today in your code . You can only use &=today only in %put statements
So something like sample_set_&today is how you refer
it worked! thank you so much!
Thanks so much! i managed to do the append but still not dynamically.
appreciate your responses!
Use proc append to add the daily data dynamically along with macro variable &today.
Example:
Proc import file="&path/&filename_&today..csv"
data=sample_&today.
/***your code here***/
;
run;
/****add daily file to your base i.e. sample (if you want to keep the history data and add daily incoming data to this)***/
proc append base=sample data=sample_&today. ;
run;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.