topic Re: Remove leading zeros ODS output in ODS and Base Reporting

Remove leading zeros ODS output

Q1983 — Thu, 29 Mar 2018 14:38:19 GMT

data want (keep= ID mydate);

set want1;

run;

OUTPUT

ID    mydate
12    09/16/2015
15    06/02/2013
16    10/06/2016

ODS TAGSETS.ExcelXP file= "c:/want,xml";

PROC REPORT DATA= want headskip split='*' wrap nowd

COLUMNS _all_;

DEFINE mydate / Display style(column)= { cellwidth=80pt just=right} "mydate";

run

I want to remove any leading zeros that may exist. The desired output would be

OUTPUT

ID    mydate
12    9/16/2015
15    6/02/2013
16    10/6/2016

Would tagattr be required for something like this??

Re: Remove leading zeros ODS output

mdavidson — Thu, 29 Mar 2018 19:41:16 GMT

You could try something like this on your "want1" dataset:

data a;
date="01jan2018"d;
newdate=catx('/',month(date),day(date),year(date));
format date mmddyys10.0;
run;

Re: Remove leading zeros ODS output

Cynthia_sas — Thu, 29 Mar 2018 23:29:05 GMT

Hi:

In the interest of completeness, you can get the results you want, in TAGSETS.EXCELXP by using a PICTURE format without using TAGATTR, as shown in the program code below:

data want (keep= ID mydate);
  infile datalines;
  input ID mydate : mmddyy.;
  format mydate date9.;
datalines;
12 09/16/2015
15 06/02/2013
16 10/06/2016
;
run;
  

proc format;
   picture dfmt (default=10) other='%m/%d/%Y' (datatype=date);
run;

ods excel file='c:\temp\wantxlx.xlsx';
ODS TAGSETS.ExcelXP file= "c:\temp\want.xml"
    style=htmlblue;

PROC REPORT DATA= want split='*' nowd;
 column id mydate mydate=d1 mydate=d2;
 DEFINE mydate / Display  "mydate*tagattr*(will get default format)"
  style(column)= {tagattr="Format:m/d/yyyy;"  just=right} ;
 define d1 / display f=dfmt. 'd1*no tagattr*only picture format'
  style(column)= {just=right} ;
 define d2 / display f=dfmt. 'd2*with tagattr*and picture format'
  style(column)= {tagattr="Format:m/d/yyyy;"  just=right} ;

run;

ods tagsets.excelxp close;
ods excel close;

The hitch with TAGSETS.EXCELXP is that as you can see below -- without a format and even using TAGATTR, with TAGSETS.EXCELXP, the output uses the SAS format for the column:

But, you can achieve what you want without making a character variable using a PICTURE format (or using TAGATTR).

Cynthia