topic Re: Got a problem in merging in SAS Studio

Got a problem in merging

YangYY — Tue, 22 Oct 2019 03:04:37 GMT

Hi,

I have a data set "world" which include 256 countries and continent. And another data set "have" include 125 countries and other variable. Now I want to attach the continent information to data"have".

So my code is

proc sort data=world;
by country;
run;

proc sort data=have;
by country;
run;

data attached;
merge have world;
by country;
run;

And the result is something like

country variables continent

1 Armenia 22 .

2 China 15 .

3 Korea 5 .

...

126 Armenia . Asia

127 China . Asia

128 Korea . Asia

What I want is

country variables continent

1 Armenia 22 Asia

2 China 15 Asia

3 Korea 5 Asia

...

Does my code have problem ?? Anyone could modify my code to get what I want?

THANK YOU

Re: Got a problem in merging

PGStats — Tue, 22 Oct 2019 04:04:08 GMT

It looks like the country names don't really match. Maybe you have invisible characters (tabs?) or leading spaces as part of the country names in one of the datasets.

Re: Got a problem in merging

YangYY — Tue, 22 Oct 2019 04:36:13 GMT

I checked the output data and I did not see the countries have leading space(for both of them). How should I modify my code to solve this problem??

Thank you

Re: Got a problem in merging

ed_sas_member — Tue, 22 Oct 2019 04:45:14 GMT

Have you checked if your country variable has a format?

you can run a proc contents to see that as follows :

proc contents data= have ;
run;
proc contents data= world ;
run;

Re: Got a problem in merging

YangYY — Tue, 22 Oct 2019 04:58:04 GMT

I don't think they have a format. What format they are supposed to have? How should I add a format to them?

Re: Got a problem in merging

Astounding — Tue, 22 Oct 2019 05:45:09 GMT

Examine the results by running a PROC PRINT without then with this statement:

format country $hex32.;

Re: Got a problem in merging

Kurt_Bremser — Tue, 22 Oct 2019 05:46:11 GMT

@YangYY wrote:

I don't think they have a format.

Don't think. KNOW. Run proc contents. This will reveal types, lengths and formats.

If you do a join or merge on character variables, they must have identical values. This means the join is case-sensitive.

How did you inspect your columns? The best way to find erratic characters is to create a new variable where you store the hex coding.

Suppose your country column has a length of 10, then do this (in both datasets):

length country_hex $20;
country_hex = put(country, $hex20.);

Then you can have a very clear picture of the contents.

Re: Got a problem in merging

YangYY — Tue, 22 Oct 2019 06:20:48 GMT

I have found a solution with using the strip function to remove the leading blank. So SAS can recognize two countries variables are the same and the merge would work.