DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

[ Edited ]

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
Solution
‎06-30-2016 11:24 AM
Super User
Posts: 9,676

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

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;

View solution in original post


All Replies
Super User
Posts: 17,819

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

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;

Occasional Contributor
Posts: 11

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

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?

Super User
Posts: 17,819

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

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. 

Occasional Contributor
Posts: 11

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

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.

 

Super Contributor
Posts: 408

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

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.

 

Occasional Contributor
Posts: 11

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

[ Edited ]

 

Respected Advisor
Posts: 4,646

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

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;
PG
Occasional Contributor
Posts: 11

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

Hi PG,

 

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

Solution
‎06-30-2016 11:24 AM
Super User
Posts: 9,676

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

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;
Occasional Contributor
Posts: 11

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

It works! Thank you very much!

Respected Advisor
Posts: 4,646

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

@Ksharp, how is this better?

PG
Occasional Contributor
Posts: 11

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

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

 

 

Super User
Posts: 9,676

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

@PGStats ,

Both the same, I just copy your code and follow my idea .  :-) 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 514 views
  • 6 likes
  • 5 in conversation