how to make a select

[Ivan555]

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:

2|2|20190320|15
4|3|20190405|5
5|1|20190406|25

that is, for each TYP field, the values of the DATE and QTY fields are required with a DATE value most preceding for the date in the condition.

please tell me, how such a request could be realised?

best regards

[Jagadishkatam · Accepted solution]

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;

[Ivan555]

Thank you very much, Jag!

Have a good weekend!

[Jagadishkatam]

Thank you @Ivan555
Could you please Mark answers as helpful and correct as appropriate. It helps future users narrow down what responses to look at

[Ivan555]

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