topic Re: How to get previous date if missing in SAS Programming

How to get previous date if missing

suresh123 — Sat, 28 Apr 2018 07:43:04 GMT

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.

Re: How to get previous date if missing

PeterClemmensen — Sat, 28 Apr 2018 08:50:15 GMT

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?

Re: How to get previous date if missing

suresh123 — Sat, 28 Apr 2018 08:53:58 GMT

Any date before the missing date

Re: How to get previous date if missing

SASKiwi — Sat, 28 Apr 2018 09:09:04 GMT

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;

Re: How to get previous date if missing

PeterClemmensen — Sat, 28 Apr 2018 09:10:32 GMT

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;

Re: How to get previous date if missing

suresh123 — Sat, 28 Apr 2018 09:19:55 GMT

Does grouping change the report?