Matching by last-but-one day in a month and company.

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Matching by last-but-one day in a month and company.

Dear All,

I have the following problem:

Dataset1                                              Dataset2

Days                  Company                      Days                Company          Other variables

31/01/1996              1                            28/01/1996             1

28/02/1996              1                            29/01/1996             1

31/03/1996              1                            30/01/1996             1

30/04/1996              1                            31/01/1996             1

                                                              26/02/1996             1

                                                              27/02/1996             1

                                                              28/02/1996             1

                                                              26/03/1996             1

                                                              27/03/1996             1

                                                              28/03/1996             1

                                                              31/03/1996             1

                                                              27/04/1996             1

                                                              28/04/1996             1

                                                              30/04/1996             1

I need to match dataset1 and dataset2 by dates and company (in my example here there is just one company, but I need to do matching for all companies in my sample). In dataset1, I have end of month dates whereas in dataset2 I have daily data, but with gaps. What I want to get is the dataset3 with the same number of rows as in dataset1, but instead of matching exactly day-by-day, I need to match pair "month end date-company" from dataset1 and pair "last-but-one (next to last) month end date-company" from dataset2.

In other words, data from dataset2 i.e "other variables" on 30/01/1996 should be put into 31/01/1996 in dataset1, data on 27/02/1996 from dataset2 should be put into 28/02/1996 in dataset1 and so on. I could use date-1 function, but my daily data are with gaps like in march or april (So I need to take 28/03/1996 content from dataset2 and put it into 31/03/1996 in dataset1).

Any help would be highly appreciated.


Accepted Solutions
Solution
‎08-12-2014 04:26 PM
Trusted Advisor
Posts: 1,228

Re: Matching by last-but-one day in a month and company.

proc sql;

create table want as

SELECT a.*, b.Days as prev_date, b.new_var

FROM ds1 a

join ds2 b on a.Company = b.Company and

b.days < a.days

group by a.company, a.days

having prev_date=max(prev_date);

quit;

View solution in original post


All Replies
Respected Advisor
Posts: 4,820

Re: Matching by last-but-one day in a month and company.

Note: I corrected your data to reflect the fact that 1996 was a leap year.

data ds1;

input Days :ddmmyy10. Company;

format Days ddmmyy10.;

datalines;

31/01/1996              1       

29/02/1996              1       

31/03/1996              1       

30/04/1996              1       

;

data ds2;

input Days :ddmmyy10. Company;

format Days ddmmyy10.;

dummy + 1;

datalines;

  28/01/1996             1

  29/01/1996             1

  30/01/1996             1

  31/01/1996             1

  26/02/1996             1

  27/02/1996             1

  28/02/1996             1

  26/03/1996             1

  27/03/1996             1

  28/03/1996             1

  31/03/1996             1

  27/04/1996             1

  28/04/1996             1

  30/04/1996             1

;

proc sql;

create table ds3 as

select *

from ds1 left join

(select * from

    (select intnx('MONTH',Days,0,"END") as eom format=ddmmyy10., Days as blDays, dummy

    from ds2

    group by calculated eom

    having Days < max(Days))

    group by eom

    having blDays = max(blDays))

on days = eom;

select * from ds3;

quit;

PG

PG
Respected Advisor
Posts: 3,156

Re: Matching by last-but-one day in a month and company.

My version. Borrowed your data. However, the Feb output is a little different.

data ds1;

input Days :ddmmyy10. Company;

format Days ddmmyy10.;

datalines;

31/01/1996              1      

29/02/1996              1      

31/03/1996              1      

30/04/1996              1      

;

data ds2;

input Days :ddmmyy10. Company;

format Days ddmmyy10.;

dummy + 1;

datalines;

28/01/1996             1

29/01/1996             1

30/01/1996             1

31/01/1996             1

26/02/1996             1

27/02/1996             1

28/02/1996             1

26/03/1996             1

  27/03/1996             1

28/03/1996             1

31/03/1996             1

27/04/1996             1

28/04/1996             1

30/04/1996             1

;

proc sql;

create table ds3 as

select *

from ds1 left join

(select * from

(select intnx('MONTH',Days,0,"END") as eom format=ddmmyy10., Days as blDays, dummy

from ds2

group by calculated eom

having Days < max(Days))

group by eom

having blDays = max(blDays))

on days = eom;

select * from ds3;

quit;


Haikuo

Super User
Posts: 9,875

Re: Matching by last-but-one day in a month and company.

How about :

data ds1;
input Days :ddmmyy10. Company;
format Days ddmmyy10.;
datalines;
31/01/1996              1       
29/02/1996              1       
31/03/1996              1       
30/04/1996              1       
;
data ds2;
input Days :ddmmyy10. Company;
format Days ddmmyy10.;
dummy + 1;
datalines;
  28/01/1996             1
  29/01/1996             1
  30/01/1996             1
  31/01/1996             1
  26/02/1996             1
  27/02/1996             1
  28/02/1996             1
  26/03/1996             1
  27/03/1996             1
  28/03/1996             1
  31/03/1996             1
  27/04/1996             1
  28/04/1996             1
  30/04/1996             1
;
data want(drop=lag_dummy);
 set ds1(in=ina) ds2;
 by company days;
 lag_dummy=lag(dummy);
 if missing(dummy) and Company=lag(Company)     then dummy=lag_dummy;
 if ina;
run;

Xia Keshan

Contributor
Posts: 71

Re: Matching by last-but-one day in a month and company.

Thank you very much for your codes.

I have not run it yet, but before doing it I have quickly gone through it and found one possible problem. Since I have companies, the last day in a month could be not be 31/01/1996 or 29/02/1996. That is, the datasets for company 2 (just example) may look like this:

data ds1;

input Days :ddmmyy10. Company;

format Days ddmmyy10.;

datalines;

29/01/1996              2      

27/02/1996              2      

31/03/1996              2      

29/04/1996              2      

;

data ds2;

input Days :ddmmyy10. Company;

format Days ddmmyy10.;

dummy + 1;

datalines;

  27/01/1996             2

  28/01/1996             2

  29/01/1996             2

  26/02/1996             2

  27/02/1996             2

  26/03/1996             2

  27/03/1996             2

  28/03/1996             2

  31/03/1996             2

  26/04/1996             2

  27/04/1996             2

  29/04/1996             2

;

As you can see, for January, February and April, the last days in a month are not calendar days. But, anyway, I need to match company and 29/01/1996 from first dataset with 28/01/1996 content from second dataset, 29/04/1996 from first dataset with 27/04/1996 content from second dataset (please keep in mind that daily data are with gaps since I am dealing with trading days). I am not sure if intnx function will work in this case.

Ksharp, will your code work in the case for company 2 described above?

Thanks a lot again for your responses. I look forward to your comments.

Respected Advisor
Posts: 3,788

Re: Matching by last-but-one day in a month and company.

data ds1;
   input Days :ddmmyy10. Company;
   format Days ddmmyy10.;
  
datalines;
31/01/1996              1      
29/02/1996              1      
31/03/1996              1      
30/04/1996              1      
29/01/1996              2     
27/02/1996              2     
31/03/1996              2     
29/04/1996              2  
29/05/1996              2      
;;;;
   run;
proc print;
  
run;

data ds2(index=(cd=(company days)));
   input Days :ddmmyy10. Company;
   format Days ddmmyy10.;
   dummy +
1;
  
datalines;
  28/01/1996             1
  29/01/1996             1
  30/01/1996             1
  31/01/1996             1
  26/02/1996             1
  27/02/1996             1
  28/02/1996             1
  26/03/1996             1
  27/03/1996             1
  28/03/1996             1
  31/03/1996             1
  27/04/1996             1
  28/04/1996             1
  30/04/1996             1
  27/01/1996             2
  28/01/1996             2
  29/01/1996             2
  26/02/1996             2
  27/02/1996             2
  26/03/1996             2
  27/03/1996             2
  28/03/1996             2
  31/03/1996             2
  26/04/1996             2
  27/04/1996             2
  29/04/1996             2
;;;;
   run;
proc print;
  
run;
data ds3;
   set ds1;
   do days2=days-1 to intnx('month',days,0,'Begin') by -1 until(_iorc_ eq 0);
      _error_ = 0;
     
set ds2(rename=(days=Days2)) key=cd;
      end;
  
if _error_ then delete;
   run;
proc print;
  
run;
8-12-2014 12-01-04 PM.png
Contributor
Posts: 71

Re: Matching by last-but-one day in a month and company.

Thanks a lot for your code.


Just one more question (I hope minor question). For daily data, I will have many observations per day i.e.

Day                                             Company                 Some other variable

28/01/1996             1               10

28/01/1996             1               11

28/01/1996             1               12

29/01/1996             1                5

29/01/1996             1                6

29/01/1996             1                7

29/01/1996             1                8

30/01/1996             1                2

30/01/1996             1                3

31/01/1996             1                20

31/01/1996             1                48

31/01/1996             1                41

26/02/1996             1                45

26/02/1996             1                37

26/02/1996             1                24

27/02/1996             1                90

27/02/1996             1                98

28/02/1996             1                32

28/02/1996             1                83

28/02/1996             1                23

28/02/1996             1                80

29/02/1996             1                59

29/02/1996             1                1


The same applies to March and April, and for company 2 with the same dates (not the calendar month end dates) as before.

So I need to get table like this:

31/01/1996              1     30/01/1996             1                2

31/01/1996              1     30/01/1996             1                3

29/02/1996              1     28/02/1996             1                32

29/02/1996              1     28/02/1996             1                83

29/02/1996              1     28/02/1996             1                23

29/02/1996              1     28/02/1996             1                80


Will your code give me table like this? The same applies for march, april, then for company 2. Thus, I need to get exactly the same table as you showed me, but with multiple values per day (taken from daily dataset i.e. from dataset2).

Look forward to getting your reply.

Respected Advisor
Posts: 3,788

Re: Matching by last-but-one day in a month and company.

data ds1;
   input Days :ddmmyy10. Company;
   format Days ddmmyy10.;
  
datalines;
31/01/1996              1      
29/02/1996              1      
31/03/1996              1      
30/04/1996              1      
29/01/1996              2     
27/02/1996              2     
31/03/1996              2     
29/04/1996              2  
29/05/1996              2      
;;;;
   run;
proc print;
  
run;

data ds2(index=(cd=(company days)));
   input Days :ddmmyy10. Company somevar;
   format Days ddmmyy10.;
   dummy +
1;
  
datalines;
28/01/1996             1               10
28/01/1996             1               11
28/01/1996             1               12
29/01/1996             1                5
29/01/1996             1                6
29/01/1996             1                7
29/01/1996             1                8
30/01/1996             1                2
30/01/1996             1                3
31/01/1996             1                20
31/01/1996             1                48
31/01/1996             1                41
26/02/1996             1                45
26/02/1996             1                37
26/02/1996             1                24
27/02/1996             1                90
27/02/1996             1                98
28/02/1996             1                32
28/02/1996             1                83
28/02/1996             1                23
28/02/1996             1                80
29/02/1996             1                59
29/02/1996             1                1
27/01/1996             2                41
28/01/1996             2                45
29/01/1996             2                37
26/02/1996             2                24
27/02/1996             2                90
26/03/1996             2                98
27/03/1996             2                32
28/03/1996             2                83
31/03/1996             2                23
26/04/1996             2                80
27/04/1996             2                59
29/04/1996             2                1
;;;;
   run;
proc print;
  
run;
data ds3;
   set ds1;
   do Days2=days-1 to intnx('month',days,0,'Begin') by -1 until(rc eq 0);
      _error_ = 0;
     
link set;
      rc = _iorc_;
     
if _iorc_ eq 0 then do until(_iorc_ ne 0);
         link set;
         end;
     
end;
  
if _error_ then delete;
   drop rc;
   return;
set:
  
set ds2(rename=(days=Days2)) key=cd;
   if _iorc_ eq 0 then output;
  
return;
  
run;
proc print;
  
run;

8-12-2014 3-18-28 PM.png
Super User
Posts: 9,875

Re: Matching by last-but-one day in a month and company.

Sure. Mine suit your request .

Super Contributor
Posts: 578

Re: Matching by last-but-one day in a month and company.

Isn't it as simple as this:

proc sql;

create table want as

select   

    t1.*

    ,t2.dummy

from

    ds1 t1, ds2 t2

where

    t1.company=t2.company

    and t2.days = (select max(days)  from ds2 where company=t1.company and days <= t1.days)

;

quit;

DaysCompanydummy
31/01/199614
29/02/199617
31/03/1996111
30/04/1996114
29/01/1996217
27/02/1996219
31/03/1996223
29/04/1996226
29/05/1996226
Solution
‎08-12-2014 04:26 PM
Trusted Advisor
Posts: 1,228

Re: Matching by last-but-one day in a month and company.

proc sql;

create table want as

SELECT a.*, b.Days as prev_date, b.new_var

FROM ds1 a

join ds2 b on a.Company = b.Company and

b.days < a.days

group by a.company, a.days

having prev_date=max(prev_date);

quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 470 views
  • 3 likes
  • 7 in conversation