Hello,
I have the following code:
options symbolgen mprint mlogic;
options mautosource
sasautos=( '/my/macro/location/folder'
sasautos
);
libname lib "/sas/dataset/folder/path/";*this is automated;
%let path_lib = %sysfunc(pathname(lib));
%put &path_lib.;
%let dataset = name_of_sas_dataset_yyyymm;
%let end_date=201707;
DATA _NULL_;
DO UNTIL(fileexist(&path_lib.&dataset._&end_date..sas7bdat));
%let end_date=%eval(&end_date-1);
END;
RUN;
so i want to have the end_date macro variable have the value of the latest dataset.
How can i have this working?
Please suggest.
Thanks
Why not query the SASHELP.VTABLE view instead of this?
Because I want to do it this way. Dont want to involve vtable in it.
To query the dictionary tables is the right thing to do.
BTW for the code you've posted:
%let end_date=%eval(&end_date-1);
What do you expect to happen if you start with an end_date like 201701? I believe you would first need to convert the string to a SAS date value before any such calculations.
I know of that part. I actually have a macro that takes care of it. I just used %eval() here for simplicity purpose.The macro that i have will go to 201612 if i have the parameter as 201701.
Thanks
%let dataset = name_of_sas_dataset;
proc sql;
select memname into: end_date
from dictionary.tables
where libname="LIB" and memname eqt upcase("&dataset.")
having input(scan(memname,-1,"_"),best.) = max(input(scan(memname,-1,"_"),best.));
quit;
@ArpitSharma wrote:
Hello,
I have the following code:
so i want to have the end_date macro variable have the value of the latest dataset.
How can i have this working?
Please suggest.
Thanks
And what is is supposed to actually do?
The most obvious problem with this approach is the missunderstanding of what macro code does. Macro code is evaluated BEFORE the code is compiled and run. That is the whole point of macro code, to generate SAS code. So you attempted to run this data step.
DATA _NULL_;
DO UNTIL(fileexist(&path_lib.&dataset._&end_date..sas7bdat));
END;
RUN;
Now that has syntax errors, but the logic error is what is most obvious. There is no way for the value passed to the FILEEXIST() function to ever change.
It will be much easier to code this if you just code it in regular SAS code. You can use the macro variables to make it more flexible, but the basic logic is in normal SAS code. No need to use FILEEXIST() when looking for datasets, use EXISTS() instead. Use the INTNX() function to move by month. Use YYMMN6. format to convert a month value into a YYYYMM string. You should also make sure to have a way to insure that it will finish.
libname lib "/sas/dataset/folder/path/";*this is automated;
%let dataset = name_of_sas_dataset_;
%let end_date='01JUL2017'd ;
data _null_;
found=0;
do offset=0 to -24 by -1 until(found);
end_date=intnx('month',&end_date,offset,'b');
if exist(cats("lib.&dataset",put(end_date,yymmn6.)) then found=1;
end;
call symputx('end_date',end_date);
run;
You might also want to add more logic to properly set the END_DATE macro when no dataset is found. Or to format the value stored in the macro variable in some other way.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.