Determining automatic file names in proc export

[hwangnyc]

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

[Reeza]

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!

[hwangnyc]

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? 

[Reeza]

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. 

[hwangnyc]

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;

[Reeza · Accepted solution]

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. 

[hwangnyc]

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? 

[Tom]

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.