The SAS Output Delivery System and reporting techniques

ODS Excel , Yea5. date format

Reply
New Contributor
Posts: 3

ODS Excel , Yea5. date format

[ Edited ]

Dears By using ODS Excel, the below code is giving 2 different results for year5. date format. Proc print is giving the right results , while for proc tabulate is not the case. To simplify the example, I have used a data set with 1 observation only. Any Idea how it can be resolved without creating new variable for year. ALi 

 

data x;
    datex='12jun18'd;
run;

ods EXCEL file="d:\temp\ODSExcel.XLSX" options(sheet_interval="NONE" 
    EMBEDDED_TITLES='ON');

proc print data=x noobs;
    format datex year5.;
    title "Proc Print";
run;

proc tabulate data=x;
    table datex, n;
    class datex;
    format datex year5.;
    Title "Proc Tabulate";
run;

ods excel close;
Super User
Posts: 24,003

Re: ODS Excel , Yea5. date format

Posted in reply to Ali_jarmak

If you could make that legible it would help. 

 

Did you try YEAR4.?

I’m not even sure what YEAR5 would mean. 

 


@Ali_jarmak wrote:
Dears By using ODS Excel, the below code is giving 2 different results for year5. date format. Proc print is giving the right results , while for proc tabulate is not the case. To simplify the example, I have used a data set with 1 observation only. Any Idea how it can be resolved without creating new variable for year. ALi data x; datex='12jun18'd; run; ods EXCEL file="d:\temp\ODSExcel.XLSX" options(sheet_interval="NONE" EMBEDDED_TITLES= 'ON') ; proc print data=x noobs; format datex year5.; title "Proc Print"; run; proc tabulate data=x ; table datex,n; class datex ; format datex year5.; Title "Proc Tabulate"; run; ods excel close;

 

New Contributor
Posts: 3

Re: ODS Excel , Yea5. date format

Thank you for your prompt reply.

I have tried year4. as you have suggested. and is giving the same issue.

 

ALi

Super User
Posts: 24,003

Re: ODS Excel , Yea5. date format

Posted in reply to Ali_jarmak

What version of SAS do you have? ODS EXCEL was pre-production until 9.4 TS1M3 and even then there were still some bugs. 

 

Your code works for me in 9.4 TS1M5. 

 

You can check your version with:

 

proc product_status;run;

@Ali_jarmak wrote:

Thank you for your prompt reply.

I have tried year4. as you have suggested. and is giving the same issue.

 

ALi


PS. I fixed your initial post for you. 

 

New Contributor
Posts: 3

Re: ODS Excel , Yea5. date format

Below is the version I'm using

 

 

operating system WX64_WKS.

For Base SAS Software ...

   Custom version information: 9.4_M3

   Image version information: 9.04.01M3P062415

Super User
Posts: 24,003

Re: ODS Excel , Yea5. date format

Posted in reply to Ali_jarmak

Unfortunately that looks like a bug then. Upgrades are free if you have a valid SAS license.

 

Otherwise you can try either rolling your own format - may work - or precalculating the year. There may also be an option to use styles to force it to a text column. 

 

Since it's fixed already, there's not much point in reporting it to SAS support either.

 


@Ali_jarmak wrote:

Below is the version I'm using

 

 

operating system WX64_WKS.

For Base SAS Software ...

   Custom version information: 9.4_M3

   Image version information: 9.04.01M3P062415


 

Super User
Posts: 13,941

Re: ODS Excel , Yea5. date format

Posted in reply to Ali_jarmak

@Ali_jarmak wrote:

Dears By using ODS Excel, the below code is giving 2 different results for year5. date format. Proc print is giving the right results , while for proc tabulate is not the case. To simplify the example, I have used a data set with 1 observation only. Any Idea how it can be resolved without creating new variable for year. ALi 

 

data x;
    datex='12jun18'd;
run;

ods EXCEL file="d:\temp\ODSExcel.XLSX" options(sheet_interval="NONE" 
    EMBEDDED_TITLES='ON');

proc print data=x noobs;
    format datex year5.;
    title "Proc Print";
run;

proc tabulate data=x;
    table datex, n;
    class datex;
    format datex year5.;
    Title "Proc Tabulate";
run;

ods excel close;

I am not surprised by anything Excel does that reformats values.

If you click in the cell with the "year" you will likely see "6/12/2018" in the Proc Print output and note that the Excel "Format Cells" will show on the Number tab that the value is displayed with a "custom" date value of YYYY and an integer value of 43263,

In the Proc Tabulate you will see "7/11/1965" and an underlying numeric value of 23934. Which is neither the SAS date numeric value of 21347.

 

The tagsets.excelxp value is appears as expected for the same code.

So we may have an ODS Excel issue or Excel itself doing something in the background...

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 172 views
  • 0 likes
  • 3 in conversation