The SAS Output Delivery System and reporting techniques

tagsets.excelxp format:dd-mmm-yy

Reply
Regular Contributor
Posts: 199

tagsets.excelxp format:dd-mmm-yy

I'm trying to create a tagattr that shows dd-mmm-yy (eg 01-Jan-08).
However, with the tagattr seen below I see 01JAN08.
How do I fix this?

ods listing close;
ods tagsets.excelxp file="C:\Documents and Settings\test_data.xls";
proc print data=claim1b noobs label;
var bu job_class total;
var date / style={tagattr="format:dd-mmm-yy"};
run;
ods tagsets.excelxp close;
ods listing;
Valued Guide
Posts: 2,177

Re: tagsets.excelxp format:dd-mmm-yy

The tagattr format is not about interpreting the date excel receives, but about the way to display it.
Sounds like excel is receiving the text string that appears for SAS dates which are formatted with date7. format. Excel doesn't seem to interpret that well for me, either.
Have you tried your proc print with a format statement, like
format claim1b yymmdd10. ;
or
format claim1b ddmmyy10. ;
or
format claim1b mmddyy10. ;
which present a string that excel should be able to recognise as a date.
SAS Super FREQ
Posts: 8,864

Re: tagsets.excelxp format:dd-mmm-yy

Hi:
I agree with Peter. Generally, I find that dates are the ONLY variable where Excel respects the SAS format. If you really wanted the date to display in Excel as:
dd-mon-yyyy

...then using a PICTURE format might do the trick. See report #1 below. Report #2 just illustrates how Excel does respect the SAS date formats.;

cynthia
[pre]
ods listing close;
proc format;
picture picd (max=11) low-high='%d-%b-%Y' (datatype=date);
run;

data bday;
infile datalines;
input name $ bday : mmddyy10.;
bday2 = bday;
format bday bday2 mmddyy10.;
return;
datalines;
alan 11/15/1960
barb 08/21/1982
carl 11/29/1984
;
run;

ods listing close;
ods tagsets.excelxp file="C:\temp\test_data.xls" style=sasweb;
proc print data=bday noobs label;
title '1) Using SAS Formats both formats will be respected by Excel';
var name bday bday2;
format bday picd11. bday2 mmddyy10.;
run;

proc print data=bday noobs label;
title '2) With Different Formats for dates';
var name bday bday2;
format bday worddate28. bday2 mmddyyd10.;
run;

ods _all_ close;
ods listing;

[/pre]
Valued Guide
Posts: 2,177

Re: tagsets.excelxp format:dd-mmm-yy

Posted in reply to Cynthia_sas
Hi Cynthia
I'm back at work trying to get excelxp to apply the tagattr format to a date.
Your code above from last year successfully applies SAS formatting (as expected of course) but does not seek any excel formatting with tagattr.
Is it not possible? (to apply with tagattr, a custom excel date format to a SAS date, that excel will apply when it opens the xml)

In my aix environment the (v1.94) tagset delivers the column of date cells with a "String" tag. No matter what date formats (SASdate and tagattr) I apply, I cannot persuade excel to apply the tagattr format :-(
Of course, once the data is in excel, I can select that date column, click text-to-columns and then click the 'finish' button and the format requested by the tagattr will be applied!
I tried to transfer numerics that will be able to be formatted by excel as a date : number of days from 1900.
dob2 = dob - "30Dec1899"d ;
It sort of works, but: see results of running this code:[pre]**************************************************
* some data with dates *
*************************************************;
data some_dates ;
set sashelp.class ;
dob = intnx( 'year', today(), -age, 's' ) +int(365*ranuni(1)) ;
attrib dob format= mmddyy10. label='date of birth' ;
* attempting to derive an excel numeric date day number ;
dob2 = dob - "30Dec1899"d ;
format dob2 z10. ;
dob3 = dhms( dob,0,0,0);
format dob3 E8601DT.
run ;

**************************************************
* routine to push table to excel formatted XML *
*************************************************;
%* establish path for excel file ;
%let path= /amex/ramp/usr/pcrawf ;

ods tagsets.excelxp file="&path/demo.dates.xml" style=egDefault RS=NONE ;
ods tagsets.excelxp options( Default_Column_Width='15'
sheet_name='demo dates' ) ;

proc print label data= some_dates noobs ;
var name age sex weight height ;
var dob: / style={tagattr="Format:dd-mmm-yy"}; ;
run;
ods tagsets.excelxp close ;[/pre]unfortunately the DOB2 approach needs the complexity of converting the date base to 1900 from 1960.

Is there some feature I can use to persuade the tagset to deliver the date as a numeric?

Microsoft spreadsheet markup language stores dates in a string like the SAS format E8601DT format, and tagged with "DateTime". Here is one DOB3 cell created by tagsets.excelxp
1996-03-28T00:00:00
If I change the "String" to "DateTime" it will surface correctly in Excel.

Have I to use the E8601DT format, and postprocess the XML ?
Or
update my version of tagsets.excelxp to recognise this format and set the type to "DateTime" instead of "String"?
Or
has author Eric got it in the plan already?

hth
peterC
SAS Super FREQ
Posts: 8,864

Re: tagsets.excelxp format:dd-mmm-yy

Peter...I can do either of these with a SAS date value

1) use a SAS PICTURE format (still a SAS format, I know)
[pre]
proc format;
picture pc low-high='%0d-%b-%0y' (datatype=date);
run;

define pdate /display f=pc. 'Date for Peter' ;
[/pre]

2) use a Microsoft format (which I cut and pasted from a 2003 XML file):
[pre]
define pdate /display f=pc. 'Date for Peter'
style(column)={tagattr="[ENG][$-409]d\-mmm\-yy;@"};
[/pre]

No matter which method I use, I get the dreaded green triangle (Number as Text) in the Excel sheet.

We'll have to see what Eric says. Is there a reason that the picture format approach won't work??? Other than the green triangle issue?

cynthia
Valued Guide
Posts: 2,177

Re: tagsets.excelxp format:dd-mmm-yy

Posted in reply to Cynthia_sas
thank you Cynthia
The picture format will work as would date11. in Sas 9.2
My concern is the "number as text" would need to be converted by the client before it will begin to work properly in excel, for example filtering and sorting. I had hoped the datatype could be determined and passed appropriately to excel.
Once compatability with 9.1.3 is not needed and reporting procedures pass datatype attributes as well as text objects no determination of datatypes in tagsets.excelxp will be needed and my wish will be fulfilled as well as delivering the XML faster.

always looking forward
peterC
Valued Guide
Posts: 2,177

Re: tagsets.excelxp format:dd-mmm-yy

a solution provided to me by SAS Customer Support, here in UK. (track [SAS 7610574465])
put the date with sas format E8601DT. and add to the Tagattr value a datatype=
but don't forget to trail the custom format with ";@" !
as in

var dob / style(data)={tagattr='typeSmiley Very Happyatetime format:dd-mmm-yyyy;@'};
as in
proc print label data= some_dates noobs ;
var name age sex weight height ;
var dob3 / style(data)={tagattr='typeSmiley Very Happyatetime format:dd-mmm-yyyy;@'};
format dob3 e8601DT.;
run;

Of course, this will need the date variable to contain a datetime value, but that is not difficult to work with.
Alternatively, proc format could create a date format in the style of E8601DT


peterC and don't forget to trail the custom format with ";@" !
(as I forgot to indicate earlier!) 14:30 GMT 27-Apr-2011


Message was edited by: Peter.C
Contributor
Posts: 71

Re: tagsets.excelxp format:dd-mmm-yy

We use a similar solution to Peter C. (above). However, with this extra step to convert to standard SAS date integer:

report_date = report_date * 86400;

then the format IS8601DT. (similar to E8601DT.) is applied to the variable, and the same tagattr is used in PROC REPORT style(column).

Q: Has anyone managed to display missing date values in Excel using this method with a space (blank) rather than a dot?

When saving a blank Excel date value as XML, Excel converts the cell format to STRING. I tried this from PROC REPORT using:

compute before group_variable;
if report_date = . then
call define(_17_, "style", "style=[tagattr='format:text']");
endcomp;

This still wrote out a dot even though MISSING=" " and other missing values in the report were correctly blank. It seems that the tagset is writing out a dot regardless. BTW our tagset is a tweaked version (by Chris Wright) from 2007.

Thanks.
Ask a Question
Discussion stats
  • 7 replies
  • 2536 views
  • 0 likes
  • 4 in conversation