BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
LisaZ1
Obsidian | Level 7

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
	;


 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Adriaan_Gouws
Obsidian | Level 7

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;

View solution in original post

4 REPLIES 4
andreas_lds
Jade | Level 19
  • 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
Obsidian | Level 7
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
Obsidian | Level 7

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
Obsidian | Level 7
Thank you for you suggestion. It works. It outputs exactly what I want! Thank you!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 846 views
  • 1 like
  • 3 in conversation