Merge duplicating values for what should be null responses

[ligbag4]

I'm trying to merge data sets from two different years that identify number of births, by race, by geographic location. For example:

Year_1

Location            Race          # of Births

Location_1          Race_1           0

Location_1          Race_2           1

Location_1          Race_3           2

Location_1          Race_4           3

Location_1          Race_5           0

Year_2

Location            Race          # of Births

Location_1         Race_1         4

Location_1         Race_2         5

Location_1         Race_3         6

Location_1         Race_4         7

Location_1         Race_5         8

Using the following code:

data merged;

merge race_year1 (rename=(count=race_year1))

race_year2 (rename=(count=race_year2));

by Location;

run;

However, the dataset output is coming out like this:

Location            Race          # of Births Year 1           # of Births Year 2

Location_1          Race_1                 1                                                  4

Location_1         Race_2                 2                                                 5

Location_1         Race_3                 3                                                 6

Location_1         Race_4                 3                                                 7

Location_1         Race_5                 3                                                 8

When I'm wanting this:

Location            Race          # of Births Year 1           # of Births Year 2

Location_1          Race_1                 0                                               4

Location_1         Race_2                 1                                                 5

Location_1         Race_3                 2                                                6

Location_1         Race_4                 3                                                7

Location_1         Race_5                 0                                                8

I've tried merging by race as well, using the following code:

data merged;

merge race_year1 (rename=(count=race_year1))

race_year2 (rename=(count=race_year2));

by Location Race;

run;

But get the same result. Anybody know how to get SAS to provide null values instead of re-ordering and duplicating?

Edit: I realize I wasn't clear in my initial question. I'm not having the issue with the re-ordering and duplication taking place in SAS. It appears that's only occurring when I'm exporting the merged dataset to an xlsx file.

Export code I'm using:

proc export data=merged

outfile="[filelocation]\merged.xlsx"

dbms=xlsx

replace;

run;

[Kurt_Bremser]

You need to merge by location and race:

data have1;
input location :$10. race $ count;
datalines;
Location_1          Race_1           0
Location_1          Race_2           1
Location_1          Race_3           2
Location_1          Race_4           3
Location_1          Race_5           0
;

data have2;
input location :$10. race $ count;
datalines;
Location_1         Race_1         4
Location_1         Race_2         5
Location_1         Race_3         6
Location_1         Race_4         7
Location_1         Race_5         8
;

data want;
merge
  have1 (rename=(count=count1))
  have2 (rename=(count=count2))
;
by location race;
run;

proc print data=want noobs;
run;

Result:

location	race	count1	count2
Location_1	Race_1	0	4
Location_1	Race_2	1	5
Location_1	Race_3	2	6
Location_1	Race_4	3	7
Location_1	Race_5	0	8

[ligbag4]

Sorry, I'm realizing I wasn't clear in my initial question. I'm not having the problem when I print the merged dataset in SAS. The issue is occurring when I export the dataset into a xlsx file.

[quickbluefish]

I suggest you run the code that @Kurt_Bremser posted and start from there.

[Kurt_Bremser]

---

@ligbag4 wrote:

Sorry, I'm realizing I wasn't clear in my initial question. I'm not having the problem when I print the merged dataset in SAS. The issue is occurring when I export the dataset into a xlsx file.

---

Why are you guys always posting only half (or sometimes, even a tenth) of the real issue????

Maxim 42!!

Please post code and log of your Excel export, so we can recreate the issue. Use the data I created with DATA steps as source, or also show us your original data in DATA steps, as I did.

[ligbag4 · Accepted solution]

I wasn't clear because I didn't realize the issue was in the export step when I posted the question. I thought it was in the merge step. Since it's an issue with a different step, I've created a new thread for it: https://communities.sas.com/t5/SAS-Programming/Proc-Export-duplicating-values-for-what-should-be-nul...

Sorry for the confusion.

[Kurt_Bremser]

PS when I run

proc export
  data=want
  file="~/want.xlsx"
  dbms=xlsx
  replace
;
run;

and open the file in Excel after downloading, it looks EXACTLY like the PROC PRINT result.

[Tom]

Make sure you are printing the right INPUT datasets.   Your listings says "YEAR1" and your code references RACE_YEAR1 instead.

Also make sure you are printing the right OUTPUT dataset.  Your code says it is creating MERGED.  So first check the SAS log and make sure that the data step completed and made a new version of MERGED and then make sure you print that new dataset and not some other dataset.