topic Re: Merge duplicating values for what should be null responses in SAS Programming

Merge duplicating values for what should be null responses

ligbag4 — Wed, 25 Feb 2026 19:12:51 GMT

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;

Re: Merge duplicating values for what should be null responses

Kurt_Bremser — Wed, 25 Feb 2026 18:52:36 GMT

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

Re: Merge duplicating values for what should be null responses

ligbag4 — Wed, 25 Feb 2026 19:11:14 GMT

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.

Re: Merge duplicating values for what should be null responses

quickbluefish — Wed, 25 Feb 2026 19:05:41 GMT

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

Re: Merge duplicating values for what should be null responses

Tom — Wed, 25 Feb 2026 19:18:17 GMT

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.

Re: Merge duplicating values for what should be null responses

Kurt_Bremser — Wed, 25 Feb 2026 19:20:43 GMT

@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.

Re: Merge duplicating values for what should be null responses

Kurt_Bremser — Wed, 25 Feb 2026 19:24:55 GMT

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.

Re: Merge duplicating values for what should be null responses

ligbag4 — Wed, 25 Feb 2026 19:47:31 GMT

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-null-responses/m-p/984072#M379652

Sorry for the confusion.