topic Re: SAS Merge and fill missing fields in SAS Programming

SAS Merge and fill missing fields

tyq1992 — Fri, 20 Sep 2019 03:48:12 GMT

Hi all,

I have two different datasets, 1 containing country and continent information and the other 1 containing continent and profit ratios.

Dataset 1:

Country Continent

China Asia

Japan Asia

Malaysia Asia

Germany Europe

Australia Oceania

Dataset 2:

Continent Profit_Margin

Asia 0.05

Europe 0.08

America 0.02

My desired results are:

Country Continent Profit_Margin

China Asia 0.05

Japan Asia 0.05

Malaysia Asia 0.05

Germany Europe 0.08

Australia Oceania .

Have tried the following code:

Data want;

merge Dataset_1 (IN=A) Dataset_2 (IN=B);

by Continent;

If A;

But the results i get are:

Country Continent Profit_Margin

China Asia 0.05

Japan Asia .

Malaysia Asia .

Germany Europe 0.08

Australia Oceania .

Appreciate any advice i could get on this. Thanks in advance!

Re: SAS Merge and fill missing fields

ChrisNZ — Fri, 20 Sep 2019 04:27:35 GMT

1. Your code if fine is the data is sorted and works are desired

2. Your second data set is not sorted.

3. What do you have on your log?

Re: SAS Merge and fill missing fields

tyq1992 — Fri, 20 Sep 2019 06:05:13 GMT

Thanks.

Have sorted the second dataset by continent. It now appears as:

Dataset 2:

Continent Profit_Margin

America 0.02

Asia 0.05

Europe 0.08

Still facing the same problem though.

Log seems fine. It reads:

"There were 5 obs read from data set work.want"

Re: SAS Merge and fill missing fields

gamotte — Fri, 20 Sep 2019 08:35:22 GMT

Hello,

You'll have to give more details than that because your code works fine :

data Dataset_1;
    length Country Continent $10;
    input Country $ Continent $;
    cards;
China Asia
Japan Asia
Malaysia Asia
Germany Europe
Australia Oceania
;
run;

data Dataset_2;
    length Continent Profit_Margin $10;
    input Continent $ Profit_Margin $;
cards;
Asia 0.05
Europe 0.08
America 0.02
;
run;

proc sort data=Dataset_1; by Continent; run;
proc sort data=Dataset_2; by Continent; run;

data want;
merge Dataset_1 (IN=A) Dataset_2 (IN=B);
by Continent;
if A;
run;

Can you give the complete log for the data step ?