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

Solved
Frequent Contributor
Posts: 75

# 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
Posts: 1,270

## 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;

All Replies
Posts: 5,535

## 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
Posts: 3,167

## 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: 10,784

## 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
;
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

Frequent Contributor
Posts: 75

## 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?

Posts: 3,852

## 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;
Frequent Contributor
Posts: 75

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

Posts: 3,852

## 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;

rc = _iorc_;

if _iorc_ eq 0 then do until(_iorc_ ne 0);
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;

Super User
Posts: 10,784

## 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;

 Days Company dummy 31/01/1996 1 4 29/02/1996 1 7 31/03/1996 1 11 30/04/1996 1 14 29/01/1996 2 17 27/02/1996 2 19 31/03/1996 2 23 29/04/1996 2 26 29/05/1996 2 26
Solution
‎08-12-2014 04:26 PM
Posts: 1,270

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