Do Until statement on decreasing date

[ArpitSharma]

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

[Reeza]

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

[ArpitSharma]

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

[Patrick]

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

[ArpitSharma]

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

[PGStats]

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

[ballardw]

---

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

[Tom]

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.