The SAS Output Delivery System and reporting techniques

merge using month and year ?

Reply
Occasional Contributor
Posts: 12

merge using month and year ?

Hi,

I have month and year variable which i extracted from date variable,  now i need o merge to other dataset which has date, but i want to merger only by their months and year not on their exact date?

how can i do that? If i try to extract year and month and try to merge by year and month, some times the results are correct, for example if it is one year worth data then i have no issue, when the dates are between two years then the problem comes, it throws me error that month variable is not properly sorted? Anyone Please help me

Super User
Posts: 19,099

Re: merge using month and year ?

1. Create a date variable using MDY() function. i.e. new_date_var=mdy(month_var, 1, year_var)

2. Use a SQL join where you use a PUT() on the join condition i.e.  put(new_date_var, monyy7.)=put(other_date, monyy7.)

Super User
Posts: 11,121

Re: merge using month and year ?

It would help to provide example code of what you have attempted and a few records from each data set that demonstrate your difficultly.

Any time you use a data step MERGE with a BY statement all data sets must be sorted by those variables.

You probably need to do something like this before the merge:

Proc sort data=dataset1; by year month;run;

Proc sort data=dataset2; by year month;run;

data merged;

     merge dataset1 dataset2;

     by year month;

run;

Super User
Posts: 9,867

Re: merge using month and year ?

Use a special option of BY.

data merged;

     merge dataset1 dataset2;

     by date groupformat;

  format date monyy7.;

run;

Xia Keshan

Ask a Question
Discussion stats
  • 3 replies
  • 1295 views
  • 9 likes
  • 4 in conversation