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.
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;
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?
Any date before the missing date
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.