merging datasets

[LisaZ1]

Hi, I want to extract some rows in one dataset(inputdata1) based on information from another datasets(inputdata2). 

inputdata1 has variables id and month1, and inputdata2 has id, month2, and cost. What I want to do is when it is the same id in inputdata1 and inputdata2, if month2 equals month1, then output. However, the total number of month for the same id is different in inputdata1 and inputdata2. And the size of these two datasets are different. I'm not sure if SAS is able to do what I want. I insert the codes below. The data want in the bottom is what I want when I have inputdata1 and inputdata2. The rest codes are my attempt, but it didn't output what I want.

Any suggestions would be very appreciated!

data inputdata1;
	input id $ month1;
	datalines;
	637 1 
	637 2 
	637 3
	637 4
	637 5
	675 1
	675 2
	675 3
	675 4
	675 5
	675 6
	675 7
	675 8
	675 9
	891 1
	891 2
	891 3
	;
run;
proc print data= inputdata1;
run;

data inputdata2;
	input id $ month2 sum;
	datalines;
	637 3 28.9
	637 7 34.7
	675 1 34.6
	675 4 35.7
	891 9 33.4
	;
run;
proc print data= inputdata2;
run;

proc sort data=inputdata1 out=inputdata1;
	by id;
run;
proc sort data=inputdata2 out=inputdata2;
	by id;
run;

data combine;
	merge inputdata1 inputdata2;
	by id;
	if month1=month2;
run;
proc print data=combine;
run;

data want;
	input id $ month2 cost;
	datalines;
	637 3 28.9
	675 1 34.6
	675 4 35.7
	;

[andreas_lds]

• rename the month variables so that they are the same
• add the month variable to by in proc sort and data step
• remove the if statement
• add the in option to both datasets in the merge statement:

merge inputdata1(in=in1) inputdata2(in=in2);

• add: if in1 and in2; to the data step

[LisaZ1]

hi, I tried to modify my codes according to what you suggest, but it does not output what I want, either. However, I tried using proc sql and it works. Thank you anyway!

[Adriaan_Gouws · Accepted solution]

Good day

You're welcome to try this alternative, using PROC SQL. Quite useful when starting to merge datasets.

proc sql;
	create table want as 
		select *
			from inputdata1 as a
				inner join inputdata2 as b
					on (a.id=b.id and a.month1=b.month2);
quit;

[LisaZ1]

Thank you for you suggestion. It works. It outputs exactly what I want! Thank you!