DATA Step, Macro, Functions and more

Do Until statement on decreasing date

Reply
Contributor
Posts: 38

Do Until statement on decreasing date

[ Edited ]

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

Super User
Posts: 19,878

Re: Do Until statement on decreasing date

Posted in reply to ArpitSharma

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

 

Contributor
Posts: 38

Re: Do Until statement on decreasing date

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

Respected Advisor
Posts: 4,173

Re: Do Until statement on decreasing date

Posted in reply to ArpitSharma

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

Contributor
Posts: 38

Re: Do Until statement on decreasing date

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

Respected Advisor
Posts: 4,937

Re: Do Until statement on decreasing date

[ Edited ]
Posted in reply to ArpitSharma
%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;
PG
Super User
Posts: 11,343

Re: Do Until statement on decreasing date

Posted in reply to ArpitSharma

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?

 

 

Super User
Super User
Posts: 7,083

Re: Do Until statement on decreasing date

[ Edited ]
Posted in reply to ArpitSharma

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.

Ask a Question
Discussion stats
  • 7 replies
  • 159 views
  • 0 likes
  • 6 in conversation