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:@'};
Use the CATX function instead of the CAT.
Also, your using CAT incorrectly, separate values with a comma, not pipes.
They both essentially give the same output. My real problem is in retaining that output in the excel file.
Using tagattr to specify the data a String may help:
tagattr='type="String" '
as part of that style override
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)
Can you post some sample data to replicate your situation?
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;
@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.
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;
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.
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;
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
Add a TAB character before it .
cat("09"x||"040,"||"048,"||"078,"||"135,"||"136,"||"137,"
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: