Desktop productivity for business analysts and programmers

PROC IMPORT changing file name

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

PROC IMPORT changing file name

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;


Accepted Solutions
Solution
‎10-02-2013 05:52 PM
PROC Star
Posts: 7,437

Re: PROC IMPORT changing file name

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;

View solution in original post


All Replies
Super User
Posts: 5,391

Re: PROC IMPORT changing file name

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)

Data never sleeps
Contributor
Posts: 30

Re: PROC IMPORT changing file name

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?

Solution
‎10-02-2013 05:52 PM
PROC Star
Posts: 7,437

Re: PROC IMPORT changing file name

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;

Contributor
Posts: 30

Re: PROC IMPORT changing file name

Can you walk me through the syntax of the sysfunc?

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

PROC Star
Posts: 7,437

Re: PROC IMPORT changing file name

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.

Contributor
Posts: 30

Re: PROC IMPORT changing file name

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.

PROC Star
Posts: 7,437

Re: PROC IMPORT changing file name

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1674 views
  • 6 likes
  • 3 in conversation