appending .csv files onto a dataset daily

[sophie07]

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;

[novinosrin · Accepted solution]

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

[sophie07]

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 🙂 

[novinosrin]

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.

[sophie07]

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.

[novinosrin]

Use &today in your code . You can only use &=today only in %put statements

[novinosrin]

So something like sample_set_&today is how you refer

[sophie07]

it worked! thank you so much!

[sophie07]

Thanks so much! i managed to do the append but still not dynamically.

appreciate your responses!

[Vish33]

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;