Proc Import XLSX file explicit Null Values

[Terho]

Hello,

I have two excel files that I am importing below:

#1

Total Balance	Target Balance
$473,480,802
$450,109,462
$427,654,020

#2

Total Balance	Target Balance
$436,301,145	$0
$340,967,812	$0
$293,301,145	$0

When, I import both and set together, SAS will translate the Null values in #1 to be 0, so my final table will be:

Total Balance	Target Balance
$436,301,145	$0
$340,967,812	$0
$293,301,145	$0
$473,480,802	$0
$450,109,462	$0
$427,654,020	$0

However, I want the null values to remain intact. I want NULL Target balances in row 4, 5, 6.

Anyone have any tips on how to keep the NULL values intact when I do the proc Import?

[ballardw]

Provide better descriptions of your two datasets. Either the results of Proc Contents or use the instructions at https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

to provide a few rows of data as datastep code for each import result.

I have a hard time believing that SAS set an actual "NULL" to 0. Did the Excel file have some sort of display set to show 0 as NULL?

[Terho]

Hm, it looks like I am importing a "referenced cell" to this source null cell.

So even though the referenced cell points to the Null field (source), the referenced cell returns a 0. Then SAS imports this 0.

So, I will look to see if Excel can keep the referenced cell null if the source cell is null. I believe this will require a special format?

Thanks,
Terrence

[RW9 · Accepted solution]

Just put an if() around the cell reference, i.e.if(refcel not null,refcell.value,""):

https://exceljet.net/excel-functions/excel-if-function

Or save your data to a decent data medium, such as CSV and read it in correctly.

[Terho]

Thank you for your reply.

I used the formula below for the referenced cell:

=IF(ISBLANK(Overlay!E4), "", Overlay!E4)

This will properly populate blanks into the cells, and SAS will now import these as nulls.

Thank you for your help!