topic Re: Reading-in New Data Everyday in Developers

Reading-in New Data Everyday

pswork — Fri, 22 May 2015 16:18:12 GMT

Hello,

I am a brand new user of SAS and I have a couple of questions about reading in my data. Everyday there is a new .txt or .csv file created and placed in a folder for me to read-in. The name is usually the current date (dd/mm/yy, ex. 5-22-15.txt), in which the file was created. There is a lot of data in each file. I would really like write a SAS program that would pull in each day's data and add it to the prior day's data. I could then delete that individual text file each day because it has been added to my main SAS program file. So on to my questions:

1) Is there any way that place the .txt in the temporary libref (work.xxxx) using the libname statement so that SAS can automatically deletes it once I am done with the session, or would deleting the actual .txt always be my responsibility?

2) Is there a way to set up an indexing counter that would always read-in the text file with today's date, or a function that pulls in the newest text file?

Thanks for the help!!

Re: Reading-in New Data Everyday

ballardw — Fri, 22 May 2015 18:29:41 GMT

1) I'm very cautious about any program that deletes files unconditionally. You never know when someone changes the layout or contents and you can spend a lot of time trying to get a conditional delete to work properly. I would be very tempted to have a separate job run manually to clean up stuff. There is a function FDELETE to delete external (non-SAS) files.

2) One way is to get the current date formatted appropriately into a macro variable and add that to the file name.

%let dstr = %sysfunc(putn(%sysfunc(today()),mmddyyd8.));

and use "c:\folder\otherfolder\Filename&dstr..txt" as needed. NOTE: the quotes must be " for the value to resolve and there are two . as the first signifies the end of the date string.

Experiment with %put "c:\folder\otherfolder\Filename&dstr..txt", looking the LOG for the resolved value to make sure it looks right. And remember that some operating systems the filename and path are case sensitive.

Re: Reading-in New Data Everyday

Steelers_In_DC — Fri, 22 May 2015 19:12:05 GMT

I agree that deleting things is not such a good idea, set it up in a back up file before deleting, or make sure that there's an IT person that has a backup... I don't usually trust that. I like having my own backup.

Another tip, seems obvious but it comes up frequently. If you run a program with an append statement in it more than once you double the data for that month/day/week/interval.

Seems so obvious but I deal with end users that do it so often. One user does it so often that I added a minute time stamp to make it easier to go in and delete iterations.

Re: Reading-in New Data Everyday

jakarman — Sat, 23 May 2015 11:59:44 GMT

you could process that txt file to a unique sas-dataset for each text-file and the rename / move that txt-file to another location. (just have a copy to fall-back)
There could be no need for having an append approach. Yes that appending is needed because it is the only option in a RDBMS environment.

SAS is different when you need to process is with a datastep or view SAS(R) 9.4 Statements: Reference, Third Edition .

As you can specify a datasetlist (that one of all needed daily txt files) you can specify the period of needed files by defining that list.
Needed to drop outdated data is just dropping those isolated datasets.
No processing for concatenation (appending) and dropping is needed and every time only the needed data (period) is accessed nothing else.

It can't become more easy and simple as that without all RDBMS OLTP restrictions.