🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 04-30-2019 06:21 AM
(1298 views)
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
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
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Thanks,
Jag
Jag
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Thanks,
Jag
Jag
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much, Jag!
Have a good weekend!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Could you please Mark answers as helpful and correct as appropriate. It helps future users narrow down what responses to look at
Thanks,
Jag
Jag
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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