topic Re: How to merge data sets with IDs of different formats? in SAS Data Management

How to merge data sets with IDs of different formats?

NonSleeper — Wed, 03 Jun 2015 02:45:06 GMT

I need to merge two data sets using two ID variables whose characteristics look like this:

* Data set 1:

IDVar1: Type=Char; Length=42; Format=Blank (i.e., nothing's seen on the description cell of PROC contents output)

IDVar2: Type=Char; Length=21; Format=Blank

*Data set 2:

IDVar1: Type=Char; Length=19; Format=Blank; Informat = $19.

IDVar2; Type=Char; Length=21; Format=Blank; Informat = $21.

I tried several ways to create identical formats among these variables in order to merge these data sets yet none has worked; that is, a lot of observations in two data sets supposed to merge with each other based on the substantive values of ID variables turned out to be separate in merged data.

For example, this is one failed attempt:

data want1; length IDVar1 $50 IDVar2 $50; set have1; run;

data want2; length IDVar1 $50 IDVar2 $50; set have2; run;

data want3; length IDVar1 $50 IDVar2 $50; merge want1 want2; by IDVar1 IDVar2; run;

Another:

data want1; format IDVar1 $50. IDVar2 $50.; set have1; run;

data want2; format IDVar1 $50. IDVar2 $50.; set have2; run;

data want3; format IDVar1 $50. IDVar2 $50.; merge want1 want2; by IDVar1 IDVar2; run;

And it also failed. I also try combining both of these as well as playing around with a few other maneuvers, such as removing informat in data set 2 but still unsuccessful. At first when these variables were at different lengths, there was a warning message on log file. But even after making them of the same format and length (so the warning message didn't show up), the result was still the same.

So can you give me a hint or solution over this task?

Re: How to merge data sets with IDs of different formats?

Miracle — Wed, 03 Jun 2015 03:21:22 GMT

Hi NonSleeper,

Not sure what the error is and it'll be good to post the log output?

In the mean time, perhaps you would like to try this?

proc sql;

alter table have1

modify IDVar1 char(50) format=$50. informat=$50.

modify IDVar2 char(50) format=$50. informat=$50.;

quit;

proc sql;

alter table have2

modify IDVar1 char(50) format=$50. informat=$50.

modify IDVar2 char(50) format=$50. informat=$50.;

quit;

proc sort data=have1; by IDVar1 IDVar2; run;

proc sort data=have2; by IDVar1 IDVar2; run;

data want; merge have1 have2; by IDVar1 IDVar2; run;

Re: How to merge data sets with IDs of different formats?

NonSleeper — Wed, 03 Jun 2015 03:47:48 GMT

Well I figure out that my raw data sets had a typo that prevented effective merging. Thanks for your help, your code works fine.