topic Re: Determining automatic file names in proc export in SAS Procedures

Determining automatic file names in proc export

hwangnyc — Wed, 13 Jul 2016 14:33:12 GMT

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

Re: Determining automatic file names in proc export

Reeza — Wed, 13 Jul 2016 14:55:51 GMT

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!

Re: Determining automatic file names in proc export

hwangnyc — Wed, 13 Jul 2016 15:02:51 GMT

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?

Re: Determining automatic file names in proc export

Reeza — Wed, 13 Jul 2016 15:07:42 GMT

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.

Re: Determining automatic file names in proc export

hwangnyc — Wed, 13 Jul 2016 15:13:57 GMT

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;

Re: Determining automatic file names in proc export

Reeza — Wed, 13 Jul 2016 15:18:26 GMT

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.

Re: Determining automatic file names in proc export

Tom — Wed, 13 Jul 2016 15:24:57 GMT

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.

Re: Determining automatic file names in proc export

hwangnyc — Wed, 13 Jul 2016 15:28:24 GMT

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?