topic Re: appending .csv files onto a dataset daily in New SAS User

appending .csv files onto a dataset daily

sophie07 — Thu, 14 Nov 2019 13:48:59 GMT

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;

Re: appending .csv files onto a dataset daily

novinosrin — Thu, 14 Nov 2019 13:59:52 GMT

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

Re: appending .csv files onto a dataset daily

sophie07 — Thu, 14 Nov 2019 14:06:30 GMT

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 🙂

Re: appending .csv files onto a dataset daily

novinosrin — Thu, 14 Nov 2019 14:17:20 GMT

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.

Re: appending .csv files onto a dataset daily

sophie07 — Thu, 14 Nov 2019 14:23:25 GMT

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.

Re: appending .csv files onto a dataset daily

sophie07 — Thu, 14 Nov 2019 14:24:39 GMT

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

appreciate your responses!

Re: appending .csv files onto a dataset daily

novinosrin — Thu, 14 Nov 2019 14:33:26 GMT

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

Re: appending .csv files onto a dataset daily

novinosrin — Thu, 14 Nov 2019 14:38:52 GMT

So something like sample_set_&today is how you refer

Re: appending .csv files onto a dataset daily

sophie07 — Thu, 14 Nov 2019 14:42:41 GMT

it worked! thank you so much!

Re: appending .csv files onto a dataset daily

Vish33 — Thu, 14 Nov 2019 14:46:07 GMT

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;