Help using Base SAS procedures

Determining automatic file names in proc export

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Determining automatic file names in proc export

Hi Everyone,

 

I have a program that I run daily and I save certain  proc freq outputs into datasets using &systime and &sysdate9 to generate the file names. What I would like to do now is export the newly created file into an excel sheet and replace the current sheet each time I run the program. I need a way to tell SAS to use the most recent dataset to export in that specific library.

 

Essentailly I want the program to go like this:

 

Step 1: Run todays Freq

Step 2: Save it as a data file  

Step 3: Export it into a sheet and replace the current sheet. 

 

What I need help with is how to tell proc export which filename to use automatically.

 

Here is the code that I have:

 

 

proc freq data=assessedkids3;
tables CaseDispositionDesc/ out=caseDispositionDaily sparse ; /*makes a dataset named caseDispositionDaily*/
title "Daily Case Disposition &sysdate ";/*&sysdate to produce date*/
run;
/*Step 3*/
data _null_;
cdate = "&SYSDATE9";
ctime = "&SYSTIME";
time_string = cdate||"_"||translate(ctime, "_", ":");
call symput('hw_time', time_string); /*name the variable between ''*/
run;
/*Step 4*/
data "C:\Case_Disposition_Dailyrun\casedis_daily\casedisposition_&hw_time";
set caseDispositionDaily;
run;

PROC EXPORT DATA = Automatically select file here
OUTFILE = "C:\dashboard.xlsx"
DBMS=xlsx REPLACE;
Sheet = "Data";
QUIT;

Any advice would be greatly appreciated! 

 

Thanks


Accepted Solutions
Solution
‎07-13-2016 11:26 AM
Super User
Posts: 17,734

Re: Determining automatic file names in proc export

Wouldn't the file created in Step be the latest one? You can't use that same path?

 

Otherwise you need to work backwards. Assign a libname to where the files are stored, get all file names and parse out date/time to find the latest dataset. 

 

You can get the list of datasets from sashelp.vtable once the libname has been assigned. 

View solution in original post


All Replies
Super User
Posts: 17,734

Re: Determining automatic file names in proc export

Isn't it the same as what you have for step 4 in your data statement. 

 

Is is there a reason your referring to the full file path rather than assigning a libname!

Contributor
Posts: 50

Re: Determining automatic file names in proc export

Hi Reeza,

 

Thanks for the response. I'm not sure what you mean regarding both statements. I'm sure my code is inefficient, could you elorbrate as to where I can improve? 

 

 

Super User
Posts: 17,734

Re: Determining automatic file names in proc export

Step 4. That dataset being created is the one you want to export?

If so, the way you reference it when creating it is the same as how you reference it to export it. 

 

Typically you see:

 

data libname.datasetname;

 

NOT file paths. You can do that, it's just not very common. 

 

 

Contributor
Posts: 50

Re: Determining automatic file names in proc export

Right, gotcha. So in step 4 the program uses a file path because it is assigning a date and time to the file name.

 

How can I get Proc Export to pick up the most recently created file?

 

/*Step 3*/
data _null_;
  cdate2 =  "&SYSDATE9";
  ctime2 = "&SYSTIME";
  time_string = cdate2||"_"||translate(ctime2, "_", ":");
  call symput('hw_time2', time_string); /*name the variable between ''*/
run;
/*Step 4*/
data "S:\CommunityHealth\DPHO-Harlem\Research, Evaluation, and Planning\Henry\EHACE\Case_Disposition_Dailyrun\counselor_daily\casedis_Coun_&hw_time2";
  set caseDisposition_counselorDaily;
run;
Solution
‎07-13-2016 11:26 AM
Super User
Posts: 17,734

Re: Determining automatic file names in proc export

Wouldn't the file created in Step be the latest one? You can't use that same path?

 

Otherwise you need to work backwards. Assign a libname to where the files are stored, get all file names and parse out date/time to find the latest dataset. 

 

You can get the list of datasets from sashelp.vtable once the libname has been assigned. 

Contributor
Posts: 50

Re: Determining automatic file names in proc export

I ended up creating a libname pointing to the path then used the the same file name as it was listed in step 4. It worked like a charm! I imagine though, since my filename has a time componenet it would only work if the program was all run together. As in if I run it at a later time the filename would be different. Does that make sense? 

Super User
Super User
Posts: 6,495

Re: Determining automatic file names in proc export

Use the automatic macro variable SYSLAST.

If I run this code

data "&path\fred.sas7bdat";
 set sashelp.class;
run;

%let mydsn=&syslast;

Then the macro variable MYDSN will have the value WC000002.FRED.  Note that the libref that SAS automatically created because I used the physical path of the file.

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 487 views
  • 1 like
  • 3 in conversation