DATA Step, Macro, Functions and more

How to get previous date if missing

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

How to get previous date if missing

 

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.


Accepted Solutions
Solution
a month ago
Super User
Posts: 3,776

Re: How to get previous date if missing

Posted in reply to suresh123

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;

View solution in original post


All Replies
PROC Star
Posts: 1,190

Re: How to get previous date if missing

Posted in reply to suresh123

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?

Occasional Contributor
Posts: 18

Re: How to get previous date if missing

Any date before the missing date

Solution
a month ago
Super User
Posts: 3,776

Re: How to get previous date if missing

Posted in reply to suresh123

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;
PROC Star
Posts: 1,190

Re: How to get previous date if missing

Posted in reply to suresh123

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;
Occasional Contributor
Posts: 18

Re: How to get previous date if missing

Does grouping change the report?
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 156 views
  • 0 likes
  • 3 in conversation