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.
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;
@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.
@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
@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
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;
@Reeza - You are a star. That worked like a charm. Thank you. Issue resolved.
@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.
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.
@ballardw indeed.
I tried the hex format and it had a series of 20 - spaces at the end
as below
2020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.