topic Do Until statement on decreasing date in SAS Programming

Do Until statement on decreasing date

ArpitSharma — Mon, 14 Aug 2017 21:53:44 GMT

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

Re: Do Until statement on decreasing date

Reeza — Mon, 14 Aug 2017 21:30:49 GMT

Why not query the SASHELP.VTABLE view instead of this?

Re: Do Until statement on decreasing date

ArpitSharma — Mon, 14 Aug 2017 21:54:57 GMT

Because I want to do it this way. Dont want to involve vtable in it.

Re: Do Until statement on decreasing date

PGStats — Mon, 14 Aug 2017 22:45:04 GMT

%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;

Re: Do Until statement on decreasing date

Patrick — Mon, 14 Aug 2017 22:33:45 GMT

@ArpitSharma

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.

Re: Do Until statement on decreasing date

ballardw — Mon, 14 Aug 2017 22:38:03 GMT

@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?

Re: Do Until statement on decreasing date

ArpitSharma — Tue, 15 Aug 2017 02:08:25 GMT

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

Re: Do Until statement on decreasing date

Tom — Tue, 15 Aug 2017 03:33:26 GMT

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.