topic Re: merging datasets in New SAS User

merging datasets

LisaZ1 — Fri, 29 Jul 2022 03:15:43 GMT

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
	;

Re: merging datasets

andreas_lds — Fri, 29 Jul 2022 06:21:02 GMT

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

Re: merging datasets

Adriaan_Gouws — Fri, 29 Jul 2022 07:59:32 GMT

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;

Re: merging datasets

LisaZ1 — Fri, 29 Jul 2022 19:03:30 GMT

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!

Re: merging datasets

LisaZ1 — Fri, 29 Jul 2022 19:04:13 GMT

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