Hoping somone has mastered this relatively simple task and can point me in th right direction. All comments welcome, and thanks in advance for your contribution.
We have an analysis process that runs against a set of dated data from two time periods, t0 and t1. What we'd like to be able to do is be able to pick up the values of t0 and t1 from the datasets and embed them in the output filename sort of like this:
where MMDDYYYY corresponds to t0 and t1 taable values for period end date respectively. The result is an automatically data driven dataset naming convention that enables finding the right dataset without having to first open it.
My researches suggest that this can possibly be done with %LET or some related technique but I have seen no example of this exact thing... surely not the first to have the reqiurement, I defer to the wisdom of the community.
What you want to do can be done with the SAS Macro facility. There are a few important things to remember about the SAS Macro facility -- it really only deals with text strings and is used to generate code. The Macro facility itself does not "do" any processing. It only generates code that goes on to the compiler.
And, below is a program that reads SASHELP.PRDSALE and makes a begin date macro variable and an end date macro variable from the dates in that one file. It sounds like you may have more than 1 file involved in your process.
You may or may not be able to do everything you need with simple SQL or %LET -- but, if you find yourself thinking "Gee, I only need to have this statement execute when some condition is true." then you may be moving into the world of writing a SAS Macro program.
The SAS Macro facility is a very powerful way to generate code. My rule of thumb, however, is to understand EXACTLY what kind of code you need to generate. So you said that you want to have something like this:
And that's good, but I would urge you to think beyond just getting the file name. In other words, where would you USE this file name:
filename wombat "CONTINUITY_SCHEDULE_MMDDYYYY_to_MMDDYYYY.txt";
set file1 file2;
ods html file="CONTINUITY_SCHEDULE_MMDDYYYY_to_MMDDYYYY.html"
because you must have a good understanding of how to make the above statements syntactically correct before you introduce macro variables into the mix. For example, it would be wrong to put quotes around your file name in the %LET statement because in the DATA step usage of the output file name, the use of quotes would be incorrect. So the correct substitution of macro variables for the file name string would be:
filename wombat "&outfl..txt";
set file1 file2;
ods html file="&outfl..html"
The SAS Macro documentation is very good. There are also a lot of other SUGI and SGF papers on using SAS Macro programs and macro variables to automate processes and generate code.
*** Get min date val and max date val and put them into;
*** macro variables.;
*** Use the format wanted for the file name when creating;
*** the macro variables.;
*** Note that SASHELP.PRDSALE has a date variable called;
*** MONTH, but it is actually a SAS date value.;
*** The span of values is from Jan 1993 to Dec 1994;
*** So, the min and max can be formatted with SAS formats;
*** to make the macro variables.;
select min(month) format=mmddyyN8.,
into :bdate, :edate
*** Look in the SAS Log for the results of these;
*** macro %LET and %PUT statements;
%let begdate = &bdate;
%let enddate = &edate;
%put the beginning date: &bdate;
%put the ending date: &edate;
%let outfl = CONTINUITY_SCHEDULE_&bdate._to_&edate;
%put outfilename is: &outfl;
** now use the macro variables in ODS HTML and PROC PRINT;
** just to do something with the 3 macro variables created;
ods html file="&outfl..html" style=sasweb;
proc print data=sashelp.prdsale(obs=15);
title "Out file= &outfl..html";
title2 "Begin = &bdate -- End = &edate";
format month mmddyy10.;
ods html close;
Just a followup...there's a difference between the format mmddyy10. (which will have slashes) and mmddyyn8. which will not have any separators. Some operating systems or software programs do not let you use slashes in names --- for example, if you were creating a SAS dataset, then slashes would be invalid in a SAS dataset name. Maybe Unix will allow you to have slashes in an external name, but I know that Windows won't and I don't think the mainframe does either.
Also, an afterthought to my post, but, if you hope to use &bdate or &edate in a WHERE clause or in any test for a date, then you need to be aware of how your dates are stored internally because you can't have this in a WHERE clause:
WHERE filedate = "01151993"d;
therefore you could not have this work in a where clause:
WHERE filedate ="&bdate"d; (if &BDATE was formatted as 01151993 ...
Thanks to both of you for your contributed thoughts.
The solution I ended up with is this, which is a little simpler than first, more complicated than second alternative. Unfortunately it still is a little weird in that the file extension has to be preceded by a blank space in the filename to work:
/*-- From first period data file get the beginning date ---*/
/*--From the second period data file get the end date ---*/
/*--- Use LET to assign global variables for these dates ----*/
%let begdate = &bdate;
%let enddate = &edate;
/*--- build the variable names into filename ---*/
file "D:\datafiles\CONTINUITY_SCHEDULE_&bdate._to_&edate .txt"
Again many thanks for your help. Much appreciated.
1) You don't need two "Proc SQL", both selects can be run from within the same "PROC SQL".
2) The formal use of a macro variable is &variable. ; the period terminates the variable identifier name. So, you don't need the "space" in your name, just two successive periods: one to terminate the variable name, and one for the "dot" before the extension.
3) Why the %let? if you need bdate and edate to be global, then simply make them global before the proc sql.
%global bdate edate;
proc sql noprint;
select ... into :bdate from ... ;
select ... into :edate from ... ;
filename outfile "D:\datafiles\CONTINUITY_SCHEDULE_&bdate._to_&edate..txt"
proc ... out=outfile ;
Unless you need different formats/values for the macro variables, one holds the date value as a number for comparison purposes in other SAS code, and one with the text format you need in naming conventions.
About #3...the extra %LET after PROC SQL is because PROC SQL preserves leading and trailing blanks...It's NOT to make them GLOBAL. See this SAS log:
363 proc sql;
364 select min(month) format=mmddyyn10.
365 into :bdate
366 from sashelp.prdsale;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
So COULD you control trailing spaces by careful choice of format size, yes. But as a best practice, if you happen to NOT choose the correct format,
then the %LET after the PROC SQL step just takes care of that for you.
Wonderful feedback, thanks to you both. Very helpful indeed. Hope this dialogue will be beneficial to the community at large. I am sticking with the solution shown above simply because I got it working that way, no suggestion of best practice I assure you !
You've had excellent advice, and I don't disagree with anything you've been told. However, if you are naming files with these date values, then I think you are using the wrong date format.
By default, file lists are usually ordered lexicographically. I would find it frustrating to get a file list that had all the January files followed by the February files, and so on. When I name files with a date, I use the format Yymmddn8. Now the files are in ascending date order, and I can quickly see if a month is missing for a given year. I can also select a group of files for a period in a year to take into some file browser application.
Misnaming is a tedious thing to fix after the fact, but it pays dividends for as long as the data are used.