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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Reeza
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User Tom
Super User

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.

 

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2465 views
  • 1 like
  • 3 in conversation