turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Proc Import XLSX file explicit Null Values

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-26-2017 11:05 AM

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?

Accepted Solutions

Solution

01-26-2017
01:11 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Terho

01-26-2017 12:13 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Terho

01-26-2017 11:19 AM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

01-26-2017 11:55 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Terho

01-26-2017 12:13 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-26-2017 01:09 PM

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!