topic Re: Proc Import XLSX file explicit Null Values in SAS Programming

Proc Import XLSX file explicit Null Values

Terho — Thu, 26 Jan 2017 16:05:51 GMT

Hello,

I have two excel files that I am importing below:

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

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 — Thu, 26 Jan 2017 16:19:12 GMT

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 — Thu, 26 Jan 2017 16:55:54 GMT

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 — Thu, 26 Jan 2017 17:13:00 GMT

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

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

Terho — Thu, 26 Jan 2017 18:09:54 GMT

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!