- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello there,
I have a data set as follows:
date Eventdate sales company
20150101 20150131 700 A
20150121 20150131 500 A
20150131 20150131 400 A
20150204 20150131 300 A
20150205 20150131 800 A
20150101 20150131 2000 B
20150201 20150131 3000 B
20150302 20150510 4400 B
20150422 20150510 3200 B
20150515 20150510 1600 B
20150611 20150510 2200 B
20150621 20150510 4400 B
I want to create two new variables: Pre and post, which are the last reported sales data before the eventdate and the first reported sales data after the eventdate, respectively. The new data should look like the following:
date Eventdate sales company pre post
20150101 20150131 700 A 500 300
20150121 20150131 500 A 500 300
20150131 20150131 400 A 500 300
20150204 20150131 300 A 500 300
20150205 20150131 800 A 500 300
20150101 20150131 2000 B 3200 1600
20150201 20150131 3000 B 3200 1600
20150302 20150510 4400 B 3200 1600
20150422 20150510 3200 B 3200 1600
20150515 20150510 1600 B 3200 1600
20150611 20150510 2200 B 3200 1600
20150621 20150510 4400 B 3200 1600
Can anyone help me? Thanks a lot!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I would use a double DO UNTIL loop:
data want;
do until(last.company);
set have;
by company;
if date < eventDate then pre = sales;
if not found and date > eventDate then do; post = sales;found=1;end;
end;
do until(last.company);
set have;
by company;
output;
end;
drop found;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
PROC SQL join, and join the table to itself.
Something like the following, which is untested:
proc sql;
create table want as
select a.*, a_pre.sales as pre, a_post.sales as post
from tablea as a
left join tablea as a_pre
a.event_month-1=a_pre.month /* pre records*/
left join table1 as a_post
and a.event_month+1 = a_post.month ;/*post_records*/
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Reeza,
Thank you for your quick reply. I just edited my post...sorry about that. Do you have any idea about my new question?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I doubt your dates are actually like that. Are they SAS dates or character fields?
My suggestion would be to convert your data to what you initially posted using a month function. Note that if your crossing years you may want to use year+month rather than just month and then use INTNX on your join instead of +/- 1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Reeza,
You're right. The dates should be year+month. I do have dates crossing years. My SAS knowledge is very limited. Could you briefly explain how to use INTNX? Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
How about this one:
data sales;
input month eventmonth sales company $;
cards;
1 3 700 A
2 3 500 A
3 3 400 A
4 3 300 A
5 3 800 A
1 5 2000 B
2 5 3000 B
3 5 4400 B
4 5 3200 B
5 5 1600 B
6 5 2200 B
7 5 4400 B
;
proc sql;
select s1.*,
(select sales from sales s2 where s1.company=s2.company and s2.month=s1.eventmonth-1) as pre,
(select sales from sales s2 where s1.company=s2.company and s2.month=s1.eventmonth+1) as post
from sales s1;
quit;
Hope this helps,
- Jan.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jan,
Thank you. I just realize I want the pre-event and post-event sales data to be more accurate to dates. I modified my question and hope someone can help me with the new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I would use a double DO UNTIL loop:
data want;
do until(last.company);
set have; by company;
if date < eventDate then pre = sales;
if missing(post) then if date > eventDate then post = sales;
end;
do until(last.company);
set have; by company;
output;
end;
run;
proc print; run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi PG,
I've tried your code and Ksharp's. Both work great! Thank you all!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I would use a double DO UNTIL loop:
data want;
do until(last.company);
set have;
by company;
if date < eventDate then pre = sales;
if not found and date > eventDate then do; post = sales;found=1;end;
end;
do until(last.company);
set have;
by company;
output;
end;
drop found;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It works! Thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Ksharp, how is this better?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
it works the same. My data set is kind of big. With the "proc print" commend, it runs very slowly. Initial I thought it doesn't work. But your code acutally works great! Thank you! Don't know how to mark both codes as solutions...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@PGStats ,
Both the same, I just copy your code and follow my idea . 🙂