topic Re: ods excel outputs '????' in character column in SAS Programming

ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:08:44 GMT

Hi,

I have a SAS dataset that has a character column . format $255.

It has values just like other character columns.

When I create an excel extract using ods excel, for this particular column it shows the values as for example . This it does for all rows but only for this column.

I tried compress, trim and strip on this column thinking it could be reacting to spaces maybe. Didnt help.

Has anyone encountered this issue? What and how can I check if there are any special characters in that column in the SAS dataset that is causing this issue.

COMMERCIAL ????????????????????????????????????????????????????????????????????????????????????????????????????

The column itself has only the value COMMERCIAL.

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:14:20 GMT

Try a test, for the column recreate the data set separately, replace the value in that cell with a newly typed value of COMMERCIAL and see what happens.

For example, if the issue was with SASHELP.HEART and the field was status, what happens if you do the following:

data heart;
set sashelp.heart;
variableIssue = "COMMERCIAL";
run;
ods excel file .......;
....

proc report data=heart;
run;

ods excel close;

If the issue doesn't remain, then try the solution below:

variable = compress(variable, , 'so');

Re: ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:21:33 GMT

@Reeza - Tried that. If I create a new column and set the value to COMMERICAL, it outputs fine into the excel.

There is something that is hidden in this column that I unable to find.

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:23:43 GMT

Correct, which is why I suggested the COMPRESS with the S option which should remove any invisible characters. You could also manually truncate the value to just COMMERCIAL.

variable = substr(variable, 1, 11); and the remainder would be truncated which should fix the issue.

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:28:20 GMT

If you really want to see what's there you can format the variable as a hex character and output it then look up the code online to see what the symbol is to remove it or understand what's happening.

data demo;
set sashelp.class (obs=3);
*add invisible tab to field;
name_original = put(name, $hex.);
if _n_ = 2 then name=catt(name, '09'x);
name_hex = put(name, $hex.);
run;

Title 'Note second record differs - has 09 (TAB) instead of 20 (Space)';
proc print data=demo;
run;

Re: ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:28:26 GMT

@Reeza I had tried the compress with s option.

That column has other values too . COMMERCIAL was just an example. There are other values like NOT FOR SEGMENT etc.

For any values in this particular column it adds these question marks. It has at least 100 question marks after the value.

Other columns with the same format just display the values with no such question marks added at the end when extracted to excel.

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:29:29 GMT

Show the values as Hex then so you can isolate the character and then compress it specifically.

Re: ods excel outputs '????' in character column

ballardw — Tue, 02 Nov 2021 22:32:43 GMT

The ? means that you have some character in the value that does not exist in the current font you are using for Excel. It may mean foreign language or just some form of non-printable character. The question mark in effect says" there is a character here but I do not know what it is or how to display it with current settings".

Many question marks means many characters.

Re: ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:35:24 GMT

@Reeza Tried that. And I basically i see the below as common in all the columns at the end(the new hex column)

2020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020

so that is space (20) like you said. Why is that the strip didnt remove it. what should i use to remove it

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:40:26 GMT

SAS adds trailing spaces always so this is expected. I'd add in a hardcoded value for COMMERCIAL and compare the output from that to one from your field. Look for where they start to differ and identify the different character there. Spaces shouldn't be the issue in Excel.

FYI - are you on SAS 9.4M3+? Before that ODS EXCEL was pre-release and buggy.

Re: ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:44:03 GMT

@ballardw indeed.

I tried the hex format and it had a series of 20 - spaces at the end

as below

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:51:07 GMT

Actually you have a ton of 00 in there, not as 20 so those are not spaces but a NULL character. It's padded with spaces as well though.
Can you show a full step of how you used COMPRESS and how it didn't work?

Re: ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:51:26 GMT

@Reeza i am on SAS 9.4M3.

I checked like you said

so while the hex value for Commerical shows - 434F4D4D45524349414C

The original column hex values shows as below. So after 4C at the end it has the series of 202020..... how do I remove the trailing 202020 434F4D4D45524349414C2020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020

Re: ods excel outputs '????' in character column

Anuz — Tue, 02 Nov 2021 22:56:43 GMT

@Reeza _ i created a new column

NEW_COLUMN = compress(OLD_COLUMN,,'so');

and then output to the excel

the old existing column showed the value . it had some spaces after COMMERICAL before the ???? started

COMMERCIAL ????????????????????????????????????????????????????????????????????????????????????????????????????

The new column displayed as below . it basically removed the spaces before the ????

COMMERCIAL????????????????????????????????????????????????????????????????????????????????????????????????????

Re: ods excel outputs '????' in character column

Reeza — Tue, 02 Nov 2021 22:57:27 GMT

Try explicitly removing the 00 and see if that solves the issue.

If you export this to excel I assume you'll see the ? on the name column but not on the name_clean column.

data demo;
set sashelp.class (obs=3);
*add invisible tab to field;
name_original = put(name, $hex.);
if _n_ = 2 then name=catt(name, '00'x);
name_hex = put(name, $hex.);
name_clean = compress(name, '00'x , 'so');
name_clean_hex = put(name_clean, $hex.);

keep name:;
run;

proc print data=demo;
run;

Re: ods excel outputs '????' in character column

Ksharp — Wed, 03 Nov 2021 12:29:07 GMT

Did your try PROC EXPORT ?

or try to change your sas encoding into UTF-8 ?

Re: ods excel outputs '????' in character column

Anuz — Wed, 03 Nov 2021 13:02:15 GMT

@Reeza - You are a star. That worked like a charm. Thank you. Issue resolved.

Re: ods excel outputs '????' in character column

Anuz — Wed, 03 Nov 2021 13:05:28 GMT

@Ksharp - no I didn't. I managed to get the NULL and spaces removed after displaying the column in hex as @Reeza suggested. Issue is now resolved.