Desktop productivity for business analysts and programmers

Import weekly files using prompt manager

Reply
Frequent Contributor
Posts: 90

Import weekly files using prompt manager

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.

Super User
Posts: 3,238

Re: Import weekly files using prompt manager

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.

Frequent Contributor
Posts: 90

Re: Import weekly files using prompt manager

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.

Super User
Super User
Posts: 7,721

Re: Import weekly files using prompt manager

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.

Super User
Super User
Posts: 7,721

Re: Import weekly files using prompt manager

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

Super User
Posts: 7,450

Re: Import weekly files using prompt manager

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 1,146

Re: Import weekly files using prompt manager

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

Frequent Contributor
Posts: 90

Re: Import weekly files using prompt manager

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.

Ask a Question
Discussion stats
  • 7 replies
  • 715 views
  • 0 likes
  • 5 in conversation