How do I retain the exact text output in excel ?

[vk_87]

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:@'};

[Reeza]

Use the CATX function instead of the CAT. 

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

[vk_87]

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

[ballardw]

Using tagattr to specify the data a String may help:

tagattr='type="String" '

as part of that style override

[vk_87]

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)

[Reeza]

Can you post some sample data to replicate your situation?

[vk_87]

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

q4

;quit;

[Reeza]

@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.

[vk_87]

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;

[ballardw]

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

<Cell><Data ss:Type="String">040048078135136137138140141 </Data></Cell>

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

[Cynthia_sas]

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;

[vk_87]

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

[Ksharp]

Add a TAB character before it .

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