How to output apostrophe correctly

[CynthiaFan]

Hi,

I have imported a data set from excel(.xlsx) to SAS and have to output part of the data back to excel again. I found that the apostrophe (') will be translated to ' in excel(.xlsx). Any recommendations on the issue?

Thank you.

Ying

[ballardw]

Please show the code you are using to create the Excel output.

That example makes me suspect you are actually creating HTML and not Excel output.

[CynthiaFan]

Hi,

Thank you for your early response. Here's my code to export to excel. Let me know where to add or delete code to leave apostrophe as is.

/*export*/

proc export data=x2017_dd

outfile="e:\MDO\Surveillance\NXS\CTC_2017_.xlsx"

dbms=xlsx replace;

sheet="x2017";

run;

Ying

[ballardw · Accepted solution]

It may be a data content issue where the character you are seeing is not a simple apostrophe.

When I run this code:

data have;
   x="Value with an ' in the middle";
run;

proc export data=have
   outfile ="x:\data\test.xlsx"
   dbms=xlsx replace;
   sheet ='Have';
run;

The result I see in Excel is

Value with an ' in the middle

You might have a Unicode character or similar that is getting mapped that way. Does the value appear as a straight up and down apostrophe in the SAS dataset, like this: '  or the source file or does it have a curl like this: ’

The curly character is a different character than a simple apostrophe.

[CynthiaFan]

Hi,

I just copy and paste the sign from original Excel file:'

I checked the original excel file, format for that problematic variable is 'general'. When read in to SAS EG,  it's read as character. I may need to change the variable's format to txt in original Excel file.

Thank you.

Ying

[TomKari]

Another option is you can run this little SAS program, with your problem character in place of the single quote.

data have;
ProblemChar = "'";
ProblemCharHex = put(ProblemChar, $hex2.);
putlog ProblemCharHex=;
run;

When I run it with a single quote, the log says

ProblemCharHex=27

[CynthiaFan]

Hi,

Thank you for all the solutions. Then it happens that when the original Excel file (xlsx created back in 2017) was opened and saved again without any changes then the apostrophe can be read in to SAS correctly. Not sure if to open and save all of those excel files since there's a lot of bunches of them.

Thank you.

Ying

[ballardw]

If you can identify the character, such as copy and paste in a SAS data set you should be able to use the TRANSLATE function to fix them. The code below uses an * to represent the offending character as seen in SAS.

data example;
   x='some string with * that I * want to change * to apostrophe';
   y='another string with * to fix';
   array ___z _character_;
   do _i_ = 1 to dim(___z);
     ___z[_i_] = translate(___z[_i_],"'","*");
   end;
run; 

The semi-obnoxious array name is just to use something unlikely to be in an existing data set to avoid accidentally using an array with the same name as a variable. Something unlikely to appear allows better automation of code to process a bunch of data sets.

[TomKari]

Be careful about using TRANSLATE. You may be in the realm of multi-byte character sets / unicode, and may need to use KTRANSLATE and the other K functions.

I fell into this snake pit a few months ago on a project; yuck!

Tom