topic Re: How to automatically update a file from a directory? in SAS Programming

How to automatically update a file from a directory?

purpleclothlady — Tue, 29 Mar 2022 18:42:49 GMT

Hi All:

How to automatically update a fil from a directory?

eg.

HAVE

libname a_location 'C:\user';
filename abc "C:\Tests\test20220303.csv"
lrecl=10000;

(AFTER A FEW WEEKS )WANT:

libname a_location 'C:\user';
filename abc "C:\Tests\test20220329.csv"
lrecl=10000;

proc import datafile=ABC
dbms=csv
out=ABC_WANT
replace;
delimiter=',';
getnames=yes;
guessingrows=max;
run;

Thank you for helping,

purple

Re: How to automatically update a file from a directory?

PaigeMiller — Tue, 29 Mar 2022 19:03:49 GMT

Can you explain how the programmer will know what file name is to be used?

I could guess, but sometimes (well almost always) its better for the original poster to state clearly the criteria to be used.

Re: How to automatically update a file from a directory?

ballardw — Tue, 29 Mar 2022 19:12:51 GMT

Unless your data source is extremely careful relying on proc import to read files creates character values of different lengths and sometimes variables that are character in one data set and numeric in another. Which means you will have issues when combining data sets and likely in other places.

Write a data step, or modify one generated by Proc Import (copy the code from the log, paste into the editor and clean up, set properties of variables as the documentation from the file source should provide).

Re: How to automatically update a file from a directory?

purpleclothlady — Tue, 29 Mar 2022 19:21:46 GMT

hi Paige:
the file name is csv file. every now and then , we have to import the updated CSV file (for example: from "test20220303.csv" to "test20220329.csv" , but I have to check manually by going to the directory (there has not a set date so I have to check manually every time when I need update the CSV file)
thanks
purple

Re: How to automatically update a file from a directory?

PaigeMiller — Tue, 29 Mar 2022 19:33:25 GMT

You can have SAS read the list of files in a folder/directory, and then have SAS determine which one to read. The method to read file names from a folder has been discussed regularly in the SAS Communities, here is one solution: https://communities.sas.com/t5/SAS-Programming/Read-in-CSV-from-folder-with-newest-date/m-p/709267#M218069

Re: How to automatically update a file from a directory?

purpleclothlady — Wed, 30 Mar 2022 12:13:12 GMT

Hi Ballardw:
Thanks so much for clarification. for the csv file, what I need is to read the latest upload file (since the old version will be moved to Archive), currently we don't need to combine data, but knowing this tip is very important.

Re: How to automatically update a file from a directory?

Tom — Wed, 30 Mar 2022 13:59:55 GMT

Once you have the list of files as a dataset then just use normal SAS coding to figure out which one is the latest one. From your description you might want to extract the date from the text of the filename itself. But you might also want to check the last modified timestamp you got from the operating system when generating the list of filenames.

Once you have picked the filename you want you can just put the name into a macro variable and use that in your code. For example by using CALL SYMPUTX() function in a data step to create the macro variable.

Re: How to automatically update a file from a directory?

ballardw — Wed, 30 Mar 2022 14:24:22 GMT

@purpleclothlady wrote:
Hi Ballardw:
Thanks so much for clarification. for the csv file, what I need is to read the latest upload file (since the old version will be moved to Archive), currently we don't need to combine data, but knowing this tip is very important.

Since you say the "old version will be moved" there could be a critical question: Are there any other files in the source folder that have the same name minus the date part? If there is only going to be one file named Testyyyymmdd.csv in that folder you can reference the file name with a with a wildcard: "C:\folder\filename*.csv" for example. That would then refer to the only file that starts with Filename and is a CSV file.

Still say you would be better off writing a data step, then you place that wildcard on the INFILE statement and the program doesn't have to be updated at all - as long as that is the only file matching that naming convention.

I use a similar wild card to read a folder that has an increasing number of files of the same name/structure to read all of them into a single file. That project receives corrected versions of the raw data occasionally so I replace the old file with the corrected data and rerun the read program to bring the corrected data into the data set.