topic how to make a select in SAS Enterprise Guide

how to make a select

Ivan555 — Tue, 30 Apr 2019 10:21:05 GMT

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

Re: how to make a select

Jagadishkatam — Tue, 30 Apr 2019 13:17:08 GMT

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;

Re: how to make a select

Ivan555 — Tue, 30 Apr 2019 14:48:58 GMT

Thank you very much, Jag!

Have a good weekend!

Re: how to make a select

Jagadishkatam — Tue, 30 Apr 2019 14:58:44 GMT

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

Re: how to make a select

Ivan555 — Tue, 30 Apr 2019 15:18:38 GMT

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