topic Re: How do I retain the exact text output in excel ? in ODS and Base Reporting

How do I retain the exact text output in excel ?

vk_87 — Thu, 10 Mar 2016 16:39:24 GMT

One of my columns in my output dataset has the following values:

040,048,078,135,136,137,138,140,141

I used the following code to get that:

cat("040,"||"048,"||"078,"||"135,"||"136,"||"137,"||"138,"||"140,"||"141") as col

But when I use ODS ExcelXP to generate excel output, I am losing the commas and it is displaying a single large number. I used the following code in proc report to retain the leading zeroes and the text format, but not able to retain the commas.

define col /Display style(column)={cellwidth=200pt
tagattr='Format:@'};

Re: How do I retain the exact text output in excel ?

Reeza — Thu, 10 Mar 2016 16:49:58 GMT

Use the CATX function instead of the CAT.

Also, your using CAT incorrectly, separate values with a comma, not pipes.

Re: How do I retain the exact text output in excel ?

ballardw — Thu, 10 Mar 2016 17:00:27 GMT

Using tagattr to specify the data a String may help:

tagattr='type="String" '

as part of that style override

Re: How do I retain the exact text output in excel ?

vk_87 — Thu, 10 Mar 2016 17:11:11 GMT

They both essentially give the same output. My real problem is in retaining that output in the excel file.

Re: How do I retain the exact text output in excel ?

vk_87 — Thu, 10 Mar 2016 17:36:28 GMT

No luck there either. tagattr="type:string" and tagattr="Format:@" are essentially for the same purpose (retaining leading zeroes and the text format). That part is working but the commas are being removed. My output in excel looks like this:

040048078135136137138140141 (commas missing)

Re: How do I retain the exact text output in excel ?

Reeza — Thu, 10 Mar 2016 17:43:01 GMT

Can you post some sample data to replicate your situation?

Re: How do I retain the exact text output in excel ?

ballardw — Thu, 10 Mar 2016 18:00:20 GMT

And the full code.

Also open the output generated by tagsets.Excelxp in a text reader, not excel. Look to see what the data type is in the generated XML.

If you don't see something like

then there might be a syntax issue with the Tagattr you are using.

Re: How do I retain the exact text output in excel ?

vk_87 — Thu, 10 Mar 2016 18:48:57 GMT

Here is a very similar and simple code:

Now I want the final dataset to be exported to excel as it is

data q4;

input claim_no amt claim_sts $;

datalines;

1234 25.00 Paid

9999 30.00 Paid

1111 25.00 Rejected

2222 50.00 Paid

;

run;

proc sql;

create table summ_q4 as

select

claim_no

,amt

,claim_sts

,catx(',','040','048','078','135','136','137','138','140','141') as Edits

from

;quit;

Re: How do I retain the exact text output in excel ?

Reeza — Thu, 10 Mar 2016 18:52:56 GMT

@vk_87 Please include your ODS tagset code.

Also, can you verify what version you're on, make sure it's the latest version.

You'll see the version in your log after you run the code.

Re: How do I retain the exact text output in excel ?

vk_87 — Thu, 10 Mar 2016 19:01:30 GMT

I am using SAS 9.2.

And I was using something like this for the ODS:

ods results off;

ods listing close;

ods tagsets.excelxp file='C:\documents\claimamt.xls' ;

ods tagsets.ExcelXP options(sheet_name="claimamt" frozen_headers= 'true' autofit_height = 'yes' );

proc report data = summ_q4 nowindows split = "*";

title;

column

claim_no

amt

claim_sts

Edits

;

define claim_no /Display style(column)={cellwidth=80pt

define amt /Display style(column)={cellwidth=80pt};

define claim_sts /Display style(column)={cellwidth=80pt};

define Edits /Display style(column)={cellwidth=200pt

tagattr="format:@"};

run;

ods tagsets.ExcelXP close;

Re: How do I retain the exact text output in excel ?

Cynthia_sas — Thu, 10 Mar 2016 19:07:17 GMT

Hi:

It does appear that Excel strips out the comma, even with type:string, but there are 2 ways around that "feature" of Excel. Please see this example:

The code below shows what I did in #2 and #3 example that Excel seems to be able to live with. Simply adding a space after the comma in CATX seems to be the easiest way to fix it.

cynthia

data new;
set sashelp.class(obs=3);
length Edits1 Edits2 Edits3 $50;
Edits1= catx(',','040','048','078','135','136','137','138','140','141');
Edits2 = catx(', ','040','048','078','135','136','137','138','140','141');
Edits3 = catx(', ','List: 040','048','078','135','136','137','138','140','141');
run;

ods tagsets.excelxp file='c:\temp\teststring.xml' style=htmlblue;
proc report data=new nowd;
column name age Edits1 Edits2 Edits3;
define name / order;
define age / display;
define Edits1 / display '1 No space after comma'
         style(column)={tagattr="type:string"};
define Edits2 / display '2 With space after comma'
         style(column)={tagattr="type:string"};
define Edits3 / display '3 Start string with character not number'
         style(column)={tagattr="type:string"};
run;
ods tagsets.excelxp close;

Re: How do I retain the exact text output in excel ?

vk_87 — Thu, 10 Mar 2016 19:56:58 GMT

Hi Cynthia,

Is there a way to get the output with no spaces or strings? I considered the option of having spaces but was turned down.

Thanks

Re: How do I retain the exact text output in excel ?

Ksharp — Fri, 11 Mar 2016 02:47:09 GMT

Add a TAB character before it .

cat("09"x||"040,"||"048,"||"078,"||"135,"||"136,"||"137,"||"138,"||"140,"||"141") as col