The SAS Output Delivery System and reporting techniques

How do I retain the exact text output in excel ?

Reply
Occasional Contributor
Posts: 6

How do I retain the exact text output in excel ?

[ Edited ]

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

Super User
Posts: 18,997

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

Use the CATX function instead of the CAT. 

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

Occasional Contributor
Posts: 6

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

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

Super User
Posts: 11,101

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

Using tagattr to specify the data a String may help:

 

tagattr='type="String" '

 

as part of that style override

Occasional Contributor
Posts: 6

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

[ Edited ]

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)

 

Super User
Posts: 18,997

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

Can you post some sample data to replicate your situation?

Occasional Contributor
Posts: 6

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

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;

Super User
Posts: 18,997

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

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

Occasional Contributor
Posts: 6

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

[ Edited ]

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;

 

Super User
Posts: 11,101

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

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.

 

SAS Super FREQ
Posts: 8,814

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

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:

excelxp_comma_list.png

 

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;

 

 

Occasional Contributor
Posts: 6

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

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

Super User
Posts: 9,854

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

Add a TAB character before it .

 

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

Ask a Question
Discussion stats
  • 12 replies
  • 1066 views
  • 0 likes
  • 5 in conversation