DATA Step, Macro, Functions and more

Proc Import XLSX file explicit Null Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Proc Import XLSX file explicit Null Values

Hello,

I have two excel files that I am importing below:

#1

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

 

#2

Total BalanceTarget 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 BalanceTarget 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?


Accepted Solutions
Solution
‎01-26-2017 01:11 PM
Super User
Super User
Posts: 7,401

Re: Proc Import XLSX file explicit Null Values

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.

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Proc Import XLSX file explicit Null Values

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?

 

Occasional Contributor
Posts: 17

Re: Proc Import XLSX file explicit Null Values

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

Solution
‎01-26-2017 01:11 PM
Super User
Super User
Posts: 7,401

Re: Proc Import XLSX file explicit Null Values

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.

Occasional Contributor
Posts: 17

Re: Proc Import XLSX file explicit Null Values

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!

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 163 views
  • 2 likes
  • 3 in conversation