08-04-2015 04:15 AM
Recently I have posting questions related to SAS EG 4.3 because my new organization is using EG instead of Base SAS.
I.T. will dump 20 files into a share folder, and one of them is like this: SG_ApproveCreditApp_20150730.txt
So I have 20 import statements pointing to these 20 files, and output to LOANS.SG_ApproveCreditApp_20150730
Every week I have to point the import statement to a new file-name. It is very manual and time consuming. For example, I have to rename SG_ApproveCreditApp_20150805.txt to LOANS.SG_ApproveCreditApp_20150805
Is there any shortcut? How can I use prompt manager? The prompt can ask me to enter text in YYYYMMDD format and the rest is automated.
08-04-2015 05:20 AM
Why not have a share folder that only contains only the 20 files you have to read, then get SAS to read their names then read each one into SAS?
Once they are successfully read move these files to an archive folder ready for the next 20 files to be dumped into the now empty folder. This means your don't need a prompt at all as SAS can process the required files automatically.
If you like this approach please let us know. There have been a lot of posts along these lines.
08-04-2015 05:53 AM
I agree, in my previous company, data was stored in a single file SG_ApproveCreditApp.txt with an additional column named as_of_date.
But in my new company, they have been timestamping weekly datasets since 2010. To keep things simple for now, I don't want to change my colleagues behaviour. Each week's file is saved as SG_ApproveCreditApp_20150730.txt.
If they want to do timeseries analysis, they will first need to merge by:
For now I want to automate upload of these text files to SAS. Unfortunately our company did not buy PROC IMPORT, else i can automate in a SAS code. I am stuck with Enterprise Guide.
08-04-2015 06:30 AM
Well,if you can't change the input then your process is always going to be "non-optimal", e.g. star badly is guarenteed to end badly.
I am not sure what you mean they did not buy "proc import", this is a base SAS function which is present in all SAS installs as far as I am aware. That being said though, I would never recommend using it as it is a guessing procedure. You are better off writing a datastep import. If the only files in that area are the 20 in question and they follow the same layout then its simple:
infile "...\*.txt" dlm=",";
If there is more than those files, then you would need to preprocess a directory listing:
filename mypipe pipe 'dir "...\*.txt" /b';
length buffer $200;
input buffer $;
Then check that list for the files you want.
If they are different format then you would need to write a datastep import for each one.
08-04-2015 05:31 AM
+1 to SASKiwi there, and you could use wildcards to read all text files in without knowing the names.
Another point however is version control. So IT are dumping some files in a directory, if you version that folder with SVN. The files would then need to be named the same each time - the concept of putting information into a filename is not the best idea - so the next set of files would just overwrite the previous ones (keeping the same name) and the version control software would keep track of the files. That way you only have one set of files, and full history on those files. This would be my preferred option, let the technology handle all the audit trail and differences, you only need to worry about importing the files.
In fact, why not take it one step further. Your IT group setup a script which runs, copies the files over to the standard area and renames to the standard filenaming, commits the files to the version control system, then batch submits your import program. You come in to find a nice new dataset and email stating xyz was run and here are the logs. Should be straight forward, almost all companies have some sort of automated import facility.
08-04-2015 07:14 AM
Since "IT" drops the files, they know the names; have your SAS import job also scheduled by IT, and the filenames supplied as environment variables.
08-04-2015 04:57 PM
Sorry, I'm late to the party.
Is the issue that you want to find all of the filenames in a folder that contain a particular date string, e.g. 20150805, and then import those specific files into SAS?
If that's the issue, it's quite doable, but involves SAS code, macros, and the external file functions. It's a bit much for a volunteer forum...do you have any experienced SAS resources who can help you with this?
First step is to have a much more detailed description of what you need.
08-05-2015 05:15 AM
Other ideas are welcomed, I did it this way:
1. Use the free add-in 'Transfer files in/out of SAS' to transfer the CSV from sharedfolder to SASman. This node utilizes prompt manager to ask me the timestamp.
2. Once the CSV is inside SASmain, use INFILE to import to library.