How to get previous date if missing

[suresh123]

I created below code to pull the particular date.

%let date =2016-12-31;
proc sql;
connect to teradata   as tera ( user=testuser password=testpass );

create table new as select * from connection tera (select acct,org from dw.act
where date= &date.);
disconnect from tera;

quit;

I want to use previous date if the mentioned date in %let is not available in the datatset.

[PeterClemmensen]

By the 'previous' date, I assume you mean the day before, so 2016-12-31 becomes 2016-12-30, right?

And what if the 2016-12-30 is not present either?

[suresh123]

Any date before the missing date

[SASKiwi · Accepted solution]

Something like this perhaps?

%let date =2016-12-31;
proc sql;
connect to teradata   as tera ( user=testuser password=testpass );
create table new as select * from connection tera (
select acct,org from dw.act as A
inner join
(select max(date) as DateMax 
 from dw.act
 where date <= &date.
) as B
on A.date = B.DateMax
);
disconnect from tera;
quit;

[PeterClemmensen]

you can do something like this

proc sql;
   create table want as
   select * from sashelp.stocks
   where date >= "01nov2002"d
   group by stock
   having date=min(date);
quit;

[suresh123]

Does grouping change the report?