topic Re: Merge data on any month in year t-1 and data on July in year t in SAS Programming

Merge data on any month in year t-1 and data on July in year t

Ruslan — Wed, 29 Jun 2016 08:40:58 GMT

Hi All,

I have two datasets that look as follows:

Dataset1

Date Company

19950131 1

19950228 1

19950331 1

19950428 1

19950531 1

19950630 1

19950731 1

19950831 1

19950929 1

19951031 1

19951130 1

19951229 1

19960131 1

19960229 1

19960329 1

19960430 1

19960531 1

19960628 1

19960731 1

19960830 1

19960930 1

19961031 1

19961129 1

19961231 1

19970131 1

19970228 1

19970331 1

19970430 1

19970530 1

19970630 1

19970731 1

19970829 1

19970930 1

19971031 1

19971128 1

19971231 1

Dataset2

Date Company Var

30Jun1995 1 10

30Dec1996 1 11

The required dataset should look like this:

Date Company Var

19950131 1 .

19950228 1 .

19950331 1 .

19950428 1 .

19950531 1 .

19950630 1 .

19950731 1 .

19950831 1 .

19950929 1 .

19951031 1 .

19951130 1 .

19951229 1 .

19960131 1 .

19960229 1 .

19960329 1 .

19960430 1 .

19960531 1 .

19960628 1 .

19960731 1 10

19960830 1 10

19960930 1 10

19961031 1 10

19961129 1 10

19961231 1 10

19970131 1 10

19970228 1 10

19970331 1 10

19970430 1 10

19970530 1 10

19970630 1 10

19970731 1 11

19970829 1 11

19970930 1 11

19971031 1 11

19971128 1 11

19971231 1 11

As you can see, for each company, I need to merge dataset1 and dataset2 such that Var from dataset2 (Var data can be available at any month in a year t i.e. not only June or Dec) is added to July of year t+1 in dataset1 and this Var value is repeated until the July of year t+2 and so on. I have data until 2015 and I have a lot of companies. So I need to do this merge for all dates and for each company.

Any help will be hugely appreciated.

Kind regards,

Ruslan

Re: Merge data on any month in year t-1 and data on July in year t

RahulG — Wed, 29 Jun 2016 09:15:49 GMT

There can be many ways to do this.

One way I can think of immediately is

1. In Dataset1, create two new columns YEAR , MONTH to store value of year and month respectively

2. In Dataset2, create two new columns YEAR , MONTH. Here store value of year in YEAR column and value 7 in month column. Hard code month value in dataset2.

3. Dataset 1 left join dataset2 on YEAR, MONTH columns. Till this point value would come only in month 7. This would be

dataset 3.

4. Sort your dataset 3 by MONTH and year. Then if month =7 has any value in var column then this Var value would be reatained until next month 7 is found.

Re: Merge data on any month in year t-1 and data on July in year t

Ruslan — Wed, 29 Jun 2016 09:18:24 GMT

Hi,

I tried to do these steps, but I do not know how to merge with July of year t+1. I can easily merge with July of year t, but do not know how to merge it with July of year t+1.

So, any piece of code will be highly appreciated.

Kind regards,

Ruslan

Re: Merge data on any month in year t-1 and data on July in year t

Ksharp — Wed, 29 Jun 2016 09:35:31 GMT

Honestly, Your logic is not clear to me.

Assuming I know what you mean.

data Dataset1;
input Date : yymmdd10.         Company ;
id=year(date)*100+month(date);
format Date  yymmdd10.;
cards;
19950131        1              
19950228        1
19950331        1
19950428        1
19950531        1
19950630        1
19950731        1
19950831        1
19950929        1
19951031        1
19951130        1
19951229        1
19960131        1
19960229        1
19960329        1
19960430        1
19960531        1
19960628        1
19960731        1
19960830        1
19960930        1
19961031        1
19961129        1
19961231        1
19970131        1
19970228        1
19970331        1
19970430        1
19970530        1
19970630        1
19970731        1
19970829        1
19970930        1
19971031        1
19971128        1
19971231        1
;
run;

data Dataset2;
input Date : date9. Company          Var;
format Date  yymmdd10.;
cards;
30Jun1995          1                 10
30Dec1996          1                 11
;
run;
data key;
 set Dataset2;
 
 start=mdy(month(lag(date)),1,year(lag(date))+1);
 stop=mdy(month(date),1,year(date)+1);
 n=coalesce(intck('month',start,stop),0);
 do i=13 to n;
  new_date=intnx('month',start,i);
  id=year(new_date)*100+month(new_date);
  output;
 end;

 do i=13 to 24;
  new_date=intnx('month',date,i);
  id=year(new_date)*100+month(new_date);
  output;
 end;
keep company id var ;
run;

data want;
 merge Dataset1(in=ina) key;
 by company id;
 if ina;
run;

Re: Merge data on any month in year t-1 and data on July in year t

Ruslan — Wed, 29 Jun 2016 10:00:02 GMT

Hi Xia,

Your code works fine, however it does not take into account the fact that in dataset2 I may have the Var value on 31Dec or 30Apr. So it does not matter on which month and year t I have the Var value available (so it may be available in June, Nov or March), this Var value should be merged with July of next year t+1 in dataset1 and repeated throughout the whole year until next June of year t+2.

Please please help me to sort out this problem.

Kind regards,

Ruslan

Re: Merge data on any month in year t-1 and data on July in year t

Ksharp — Thu, 30 Jun 2016 03:30:47 GMT

Sorry. As I said , I don't understand your logic clearly . I konw what you said these .

Show me an example to prove where is my code wrong .

Re: Merge data on any month in year t-1 and data on July in year t

Ruslan — Thu, 30 Jun 2016 12:57:14 GMT

Hi All,

Different problem now!!!

I have the dataset that looks like this:

Date Company Var

199601 1 .

199602 1 .

199603 1 .

199604 1 .

199605 1 .

199606 1 10

199607 1 .

199608 1 .

199609 1 .

199610 1 .

199611 1 .

199612 1 .

199701 1 .

199702 1 .

199703 1 .

199704 1 .

199705 1 .

199706 1 11

199707 1 .

199708 1 .

199709 1 .

199710 1 .

199711 1 .

199712 1 .

The required dataset should look like this:

Date Company Var

199601 1 .

199602 1 .

199603 1 .

199604 1 .

199605 1 .

199606 1 .

199607 1 10

199608 1 10

199609 1 10

199610 1 10

199611 1 10

199612 1 10

199701 1 10

199702 1 10

199703 1 10

199704 1 10

199705 1 10

199706 1 10

199707 1 11

199708 1 11

199709 1 11

199710 1 11

199711 1 11

199712 1 11

So what I need to do is for each company, to repeat the same value that is available in June, year t, for next months within a year until June, year t+1. Please note that I need to use Var value in June, but to start from July, year t, and repeat the value until June, year t+1. I have many months and many companies.

Any help will be hugely appriciated.

Kind regards,

Ruslan

Re: Merge data on any month in year t-1 and data on July in year t

Ksharp — Sat, 02 Jul 2016 02:31:10 GMT

@Reeza has already post this question for you, I have already post the new code. Find it in this forum on your own.