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