Proc Export duplicating values for what should be null responses

[ligbag4]

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.

[Kurt_Bremser]

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.

[ligbag4 · Accepted solution]

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

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.

[Kurt_Bremser]

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.

[Tom]

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.

[ligbag4]

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.

[Tom]

---

@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);