topic Re: How to create variables with data in the pre-event month and post-event month? in SAS Programming

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

YoYo2015 — Thu, 30 Jun 2016 01:08:48 GMT

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!

Re: How to create variables with data in the pre-event month and post-event month?

Reeza — Wed, 29 Jun 2016 21:06:55 GMT

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;

Re: How to create variables with data in the pre-event month and post-event month?

YoYo2015 — Wed, 29 Jun 2016 21:09:21 GMT

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?

Re: How to create variables with data in the pre-event date and post-event date?

jklaverstijn — Wed, 29 Jun 2016 21:11:14 GMT

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.

Re: How to create variables with data in the pre-event month and post-event month?

Reeza — Wed, 29 Jun 2016 21:16:20 GMT

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.

Re: How to create variables with data in the pre-event date and post-event date?

YoYo2015 — Wed, 29 Jun 2016 21:18:21 GMT

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.

Re: How to create variables with data in the pre-event month and post-event month?

YoYo2015 — Wed, 29 Jun 2016 21:27:25 GMT

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.

Re: How to create variables with data in the pre-event date and post-event date?

PGStats — Wed, 29 Jun 2016 21:51:20 GMT

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;

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

Ksharp — Thu, 30 Jun 2016 04:33:29 GMT

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;

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

YoYo2015 — Thu, 30 Jun 2016 15:24:49 GMT

It works! Thank you very much!

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

PGStats — Thu, 30 Jun 2016 15:52:19 GMT

@Ksharp, how is this better?

Re: How to create variables with data in the pre-event date and post-event date?

YoYo2015 — Thu, 30 Jun 2016 16:09:00 GMT

Hi PG,

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

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

YoYo2015 — Thu, 30 Jun 2016 16:14:08 GMT

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

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

Ksharp — Fri, 01 Jul 2016 00:49:51 GMT

@PGStats ,

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