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!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.