topic Re: Import weekly files using prompt manager in SAS Enterprise Guide

Import weekly files using prompt manager

hellind — Tue, 04 Aug 2015 08:15:59 GMT

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.

Re: Import weekly files using prompt manager

SASKiwi — Tue, 04 Aug 2015 09:20:02 GMT

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.

Re: Import weekly files using prompt manager

RW9 — Tue, 04 Aug 2015 09:31:44 GMT

+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.

Re: Import weekly files using prompt manager

hellind — Tue, 04 Aug 2015 09:53:14 GMT

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:

data SG_ApproveCreditApp;

set SG_ApproveCreditApp_201:;

run;

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.

Re: Import weekly files using prompt manager

RW9 — Tue, 04 Aug 2015 10:30:51 GMT

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:

data want;

infile "...\*.txt" dlm=",";

input ....;

run;

If there is more than those files, then you would need to preprocess a directory listing:

filename mypipe pipe 'dir "...\*.txt" /b';

data list;

length buffer $200;

infile mypipe;

input buffer $;

run;

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.

Re: Import weekly files using prompt manager

Kurt_Bremser — Tue, 04 Aug 2015 11:14:11 GMT

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.

Re: Import weekly files using prompt manager

TomKari — Tue, 04 Aug 2015 20:57:00 GMT

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.

Tom

Re: Import weekly files using prompt manager

hellind — Wed, 05 Aug 2015 09:15:01 GMT

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.