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

Proc Export duplicating values for what should be null responses

ligbag4 — Wed, 25 Feb 2026 19:29:17 GMT

Previously posted as a Merge issue, but have now realized it's an Export issue, so staring a new thread:

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

Year1

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

Year2

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 year1 (rename=(count=year1))

year2 (rename=(count=year2));

by Location Race;

run;

In SAS, when I print the merged data set, I get the following (which is what I want):

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

Location_1 Race_1 . 4

Location_1 Race_2 1 5

Location_1 Race_3 2 6

Location_1 Race_4 3 7

Location_1 Race_5 . 8

However, when I then export this data as an excel file, I get the following:

Location Race year1 year2

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

Export code I'm using:

proc export data=merged

outfile="[filelocation]\merged.xlsx"

dbms=xlsx

replace;

run;

Apologies for the confusion on the first thread. I didn't realize the issue was with the export step when I initially posted it.

Re: Proc Export duplicating values for what should be null responses

Kurt_Bremser — Wed, 25 Feb 2026 19:54:33 GMT

As I already posted in your other thread, the data in Excel looks exactly like the PROC PRINT output.

Run my code and start from there.

Re: Proc Export duplicating values for what should be null responses

ligbag4 — Wed, 25 Feb 2026 19:54:36 GMT

Through something @Kurt_Bremser suggested on my initial thread here: https://communities.sas.com/t5/SAS-Programming/Merge-duplicating-values-for-what-should-be-null-responses/m-p/984073#M379653

I ended up realizing the issue was occurring because SAS was defaulting "0" births as ".", which excel couldn't register as a value. So, I ended up defining a new variable as so:

data merged_2;

set merged;

if year1="" then year1_count=0;

else year1_count=year1;

if year2="" then year2_count=0;

else year2_count=year2;

run;

Then exported to excel with:

proc export data=merged_2

outfile="[filelocation]\merged_2.xlsx"

dbms=xlsx

replace;

run;

This solved the issue, and the year1_count and year2_count variables contained the correct values.

Again, sorry for the lack of information on the first threat. Should out to @Kurt_Bremser for providing some code that sparked the solution.

Re: Proc Export duplicating values for what should be null responses

Kurt_Bremser — Wed, 25 Feb 2026 20:01:27 GMT

You probably have

options missing=0;

set, which means missing values are always displayed as zeroes, and this "hides" the missing values from the unwary user.

That's why we always present datasets as DATA step code, as that prevents any ambiguities. DATALINES don't lie.

Re: Proc Export duplicating values for what should be null responses

Tom — Wed, 25 Feb 2026 21:03:55 GMT

That does not look correct.

That data step looks incorrect.

Is your YEAR1 variable NUMERIC or CHARACTER?

If it is NUMERIC then your IF condition is invalidly comparing a single space character to a number.

If it is CHARACTER then the statement executed by the ELSE is assigning a character value to a NUMERIC variable.

Re: Proc Export duplicating values for what should be null responses

ligbag4 — Wed, 25 Feb 2026 21:28:42 GMT

year1 and year 2 are both numeric. I'm not sure why there wasn't an error, or an issue with the if/else statement, but it seemed to work and the excel export had the correct values for each race and year.

Re: Proc Export duplicating values for what should be null responses

Tom — Wed, 25 Feb 2026 23:09:10 GMT

@ligbag4 wrote:

year1 and year 2 are both numeric. I'm not sure why there wasn't an error, or an issue with the if/else statement, but it seemed to work and the excel export had the correct values for each race and year.

SAS will try to convert text to numbers or numbers to text , but it should report in the SAS log where it had to do that.

If you want to compare a number to the normal missing value then do that.

if year1 = . then ...

You could also use the MISSING() function which will work on both character and numeric variables. And for numeric variable is will also work for special missing values like .A etc.

if missing(year1) then ...

You could also use the COALESCE() function which would eliminate the need for the IF/THEN logic. Or the need for making a new variable.

year1 = coalesce(year1,0);