BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EarlyCode
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
LinusH
Tourmaline | Level 20

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
EarlyCode
Fluorite | Level 6

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
Opal | Level 21

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
Fluorite | Level 6

Can you walk me through the syntax of the sysfunc?

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

art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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