BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Terho
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
ballardw
Super User

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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!

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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