BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NonSleeper
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Miracle
Barite | Level 11

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

2 REPLIES 2
Miracle
Barite | Level 11

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;


NonSleeper
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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