BookmarkSubscribeRSS Feed
pkmkart
Calcite | Level 5

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

3 REPLIES 3
Reeza
Super User

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

ballardw
Super User

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;

Ksharp
Super User

Use a special option of BY.

data merged;

     merge dataset1 dataset2;

     by date groupformat;

  format date monyy7.;

run;

Xia Keshan

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
  • 3 replies
  • 3830 views
  • 9 likes
  • 4 in conversation