topic Re: Merge to data sets where one has additional variables in SAS Data Management

Merge to data sets where one has additional variables

victhor — Fri, 18 Sep 2020 14:57:33 GMT

Good Morning

I am new to SAS but have quite a bit of experience with SQL (Oracle and SQL Server).

I have two datasets that I am trying to merge together, but one has a couple of additional variables.

The first something like

id	name	street	city	state
1	aaa	111	cityname	xx
2	bbb	222	cityname	xx
3	ccc	333	cityname	xx
4	ddd	444	cityname	xx

The second something like:

id	name	street	apt	city	county	state
5	eee	555	#313	cityname	countyname	xx
6	fff	666		cityname	countyname	xx
7	ggg	777	#a	cityname	countyname	xx
8	hh	888		cityname	countyname	xx

And this is what I want:

id	name	street	apt	city	county	state
1	aaa	111		cityname		xx
2	bbb	222		cityname		xx
3	ccc	333		cityname		xx
4	ddd	444		cityname		xx
5	eee	555	#313	cityname	countyname	xx
6	fff	666		cityname	countyname	xx
7	ggg	777	#a	cityname	countyname	xx

I can do it in proc sql (union accounting for the creating the new variables in the first table) but I was wondering if there is a simpler way with native SAS code.

I have read a lot but can't find a solution. This may be sort of duh 101 stuff so my apologies for my ignorance in advance. 😁

Thanks!

Re: Merge to data sets where one has additional variables

ballardw — Fri, 18 Sep 2020 15:41:22 GMT

You are not "merging" , which is a row , usually matching, operation. This is appending or stacking.

With different variables then

data want;
   set have1
         have2
        <other data sets if needed>
   ;
run;

Warning: same named variables in the two sets must be the same type, either numeric or character in both.

Warning: if the same named variables are character but have different lengths you WILL get a warning in the log about mismatched lengths and possible truncation of data. The easiest fix in that case is to place a LENGTH statement for the variable before the set statement to set the length to the longest needed.

Suppose one of the sets has a different length then you would use code like below to set the length to the longer of the two sets.

data want;
   length street $ 45;
   set have1
         have2
        <other data sets if needed>
   ;
run;

Re: Merge to data sets where one has additional variables

victhor — Fri, 18 Sep 2020 16:07:08 GMT

Thanks!!!!