I am trying to select data based off of date and id from another table and summing a third column. Hopefully the below illustrates what I'm trying to do. data reference_table;
infile datalines dlm='#';
input id & $1. date date7. product $ dollars;
format date ddmmyy.;
datalines;
1# 01JAN17# shoe# 200
1# 01Mar18# shoe# 200
1# 01Sep15# shoe# 200
2# 01Feb19# shoe# 200
2# 01Jul18# shoe# 200
2# 01Jun17# tops# 200
3# 01Aug14# tops# 300
3# 01Dec13# tape# 200
4# 01May17# tape# 200
4# 01Sep17# limo# 800
5# 01Apr16# hats# 500
5# 01Oct18# pens# 500
;
run;
data new_month_ids;
infile datalines dlm='#';
input id & $1. date date7.;
format date ddmmyy.;
datalines;
1# '01Jan17'd
2# '13Feb19'd
3# '28Dec13'd
4# '27Jun17'd
;
run; From these two tables I am trying to do a query involving both which selects all the id's that appear in the "new_month_ids" table, and sum all of the dollars from the reference table that occurred within two years of the date in the "new_month_ids" table by both id and product. I believe the below table is what the final result should look like. data want;
infile datalines dlm='#';
input id & $1. product $ dollars;
datalines;
1# shoe# 400
2# shoe# 200
2# tops# 200
3# tape# 200
4# tape# 200
4# limo# 800
;
run; Notice for example, for id 1 we only sum two of the lines because those are the only two that appear within the two year time frame 01Jan2015-01Jan2017. Below is my attempt at this, but when I run this code it returns no values. I have a feeling this might be related to my date format in the intnx function but I haven't figured it out yet. proc sql;
create table test as
select a.id,
a.product,
sum(a.dollars) as dollars
from reference_table a left join new_month_ids b on
(a.id=b.id)
where a.id in (select distinct b.id from new_month_ids b)
And
intnx('year',b.date,-2,'same')<=a.date<=b.date
group by a.id,
a.product
;
quit; Any help is appreciated.
... View more
