BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
YoYo2015
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

13 REPLIES 13
Reeza
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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
Obsidian | Level 7

 

PGStats
Opal | Level 21

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
YoYo2015
Obsidian | Level 7

Hi PG,

 

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

Ksharp
Super User

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
Obsidian | Level 7

It works! Thank you very much!

YoYo2015
Obsidian | Level 7

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
Super User

@PGStats ,

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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