How to create variables for the pre-event date and post-event date?

[YoYo2015]

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!

[Reeza]

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;

[YoYo2015]

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?

[Reeza]

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. 

[YoYo2015]

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.

[jklaverstijn]

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.

[YoYo2015]

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.

[PGStats]

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;

[YoYo2015]

Hi PG,

I've tried your code and Ksharp's. Both work great! Thank you all! 

[Ksharp · Accepted solution]

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;

[YoYo2015]

It works! Thank you very much!

[PGStats]

@Ksharp, how is this better?

[YoYo2015]

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...

[Ksharp]

@PGStats ,

Both the same, I just copy your code and follow my idea .  🙂