PROC IMPORT changing file name

[EarlyCode]

I have a .sas program set up to import a dataset from excel, run analysis, and export the result to a new tab in the same excel file.

However, everymonth the file name changes. So last month the file was File-2013090101(1).XLS, this month it will be File-2013100101(1).XLS.

Always in the format of "File-YYYYMMDD01(1).XLS" for the first of the current month. I don't create the file, so I have no control over the naming convention.

Currently, everytime I run the program I have to first go into the code and manually update the file name to the new name. Is there a way to automate that so when the file becomes available I just need to execute the .sas program and it will automatically select the newest file?

I've tried searching for PROC IMPORT help, but I couldn't find this scenerio. So naturally if this was previously discussed I appologize.

PROC IMPORT OUT= WORK.TABLEABC

          DATAFILE= "<FILE-PATH>\FILE-2013090101(1).XLS"

          DBMS=EXCEL REPLACE;

     SHEET= "sheet1$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

Run;

[LinusH]

You could either use prompts which lets you insert the file name, or you could use date functions to calculate the file name based on the current date,  and store the result in a macro variable (which you reference in proc import)

[EarlyCode]

I'm still new to sas, so I appreciate your help.

The macro variable sounds like what I'm looking for this scenerio, while the prompt could actually be useful for another program I made.

Do you have reference links or examples that could help me understand how to do this?

[art297 · Accepted solution]

I don't think you need a macro variable.  Couldn't you accomplish what you want by simply using something like:

PROC IMPORT OUT= WORK.TABLEABC

          DATAFILE= "d:\art\FILE-%sysfunc(date(),yymmn6.)0101(1).XLS"

          DBMS=EXCEL REPLACE;

     SHEET= "sheet1$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

Run;

[EarlyCode]

Can you walk me through the syntax of the sysfunc?

Because that seems to work, and I would love to understand how.

[art297]

Not a lot to walk through.  %sysfunc is a macro function that lets one execute sas functions.  Its specs are:

%SYSFUNC (function(argument-1 <...argument-n>)<, format>)

and it is documented at:SAS(R) 9.2 Macro Language: Reference

In this case (i.e., -%sysfunc(date(),yymmn6.)          ), it is simply executing the date() function (i.e., returning today's date) using the yymmn6. format, which will return 201310

Since you filenames always contain FILE-yyyymm0101(1).xls, that was all that varied.

[EarlyCode]

And the yymmn6 is specifying the format to give the year first, then month, and n6= contained in 6 characters?

So if it was yymmddn6 it would return 131002, or if I needed it in another order, mmyyn6 would return 102013?

Varying file names by just the date is common around here, but they don't always follow the same format. So this should be very useful.

[art297]

The wide variety and versatility of SAS formats, and the fact that you can always create your own when needed, are two of the features that have always made SAS so versatile.

For an overview of most of the available formats take a look at: SAS(R) 9.3 Formats and Informats: Reference