hello!
sorry, the question may seem very simple, but I am new with SAS and seems cant to do the task in allotted time
writing on SQLLite (https://www.jdoodle.com)
my table looks like
create table 't1' (id int, typ int, dt date, qty int); insert into t1 values (1, 2, '20190325', 50), (2, 2, '20190320', 15), (3, 3, '20190401', 50), (4, 3, '20190405', 5), (5, 1, '20190406', 25), (6, 2, '20190411', 5), (7, 3, '20190412', 15); select id, typ, dt, qty from t1;I have to make a select to this table with the condition on the date '20190410' and as a result I want to see this talbe:
proc sql;
create table t1 (id int, typ int, dt char(8), qty int);
insert into t1
values (1, 2, '20190325', 50)
values (2, 2, '20190320', 15)
values (3, 3, '20190401', 50)
values (4, 3, '20190405', 5)
values (5, 1, '20190406', 25)
values (6, 2, '20190411', 5)
values (7, 3, '20190412', 15);
create table t2 as select id, a.typ, input(dt,yymmdd8.) as date format=date9.,dt, qty
from t1(where=(input(dt,yymmdd8.) < '10Apr2019'd )) a
inner join (
select typ, max(input(dt,yymmdd8.)) as MaxDate
from t1(where=(input(dt,yymmdd8.) < '10Apr2019'd))
group by typ
) b on input(a.dt,yymmdd8.)=b.MaxDate;
quit;
proc sql;
create table t1 (id int, typ int, dt char(8), qty int);
insert into t1
values (1, 2, '20190325', 50)
values (2, 2, '20190320', 15)
values (3, 3, '20190401', 50)
values (4, 3, '20190405', 5)
values (5, 1, '20190406', 25)
values (6, 2, '20190411', 5)
values (7, 3, '20190412', 15);
create table t2 as select id, a.typ, input(dt,yymmdd8.) as date format=date9.,dt, qty
from t1(where=(input(dt,yymmdd8.) < '10Apr2019'd )) a
inner join (
select typ, max(input(dt,yymmdd8.)) as MaxDate
from t1(where=(input(dt,yymmdd8.) < '10Apr2019'd))
group by typ
) b on input(a.dt,yymmdd8.)=b.MaxDate;
quit;
Thank you very much, Jag!
Have a good weekend!
no problem, done.
I am thinking, the topic name is not informative.. sorry.
next time I create a new one, I will think how formulate the topic name more correctly
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.