SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to merge data sets with IDs of different formats?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How to merge data sets with IDs of different formats?

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?


Accepted Solutions
Solution
‎06-02-2015 11:21 PM
Regular Contributor
Posts: 228

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

Posted in reply to NonSleeper

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;


View solution in original post


All Replies
Solution
‎06-02-2015 11:21 PM
Regular Contributor
Posts: 228

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

Posted in reply to NonSleeper

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;


Frequent Contributor
Posts: 75

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 389 views
  • 0 likes
  • 2 in conversation