BookmarkSubscribeRSS Feed
vk_87
Fluorite | Level 6

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

12 REPLIES 12
Reeza
Super User

Use the CATX function instead of the CAT. 

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

vk_87
Fluorite | Level 6

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

ballardw
Super User

Using tagattr to specify the data a String may help:

 

tagattr='type="String" '

 

as part of that style override

vk_87
Fluorite | Level 6

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
Super User

Can you post some sample data to replicate your situation?

vk_87
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6

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
Super User

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
SAS Super FREQ

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;

 

 

vk_87
Fluorite | Level 6

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
Super User

Add a TAB character before it .

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3296 views
  • 0 likes
  • 5 in conversation