topic Re: ODS Excel , Yea5. date format in ODS and Base Reporting

ODS Excel , Yea5. date format

Ali_jarmak — Thu, 21 Jun 2018 01:15:30 GMT

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;

Re: ODS Excel , Yea5. date format

Reeza — Thu, 21 Jun 2018 00:14:55 GMT

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;

Re: ODS Excel , Yea5. date format

Ali_jarmak — Thu, 21 Jun 2018 00:38:38 GMT

Thank you for your prompt reply.

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

ALi

Re: ODS Excel , Yea5. date format

Reeza — Thu, 21 Jun 2018 01:17:40 GMT

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.

Re: ODS Excel , Yea5. date format

Ali_jarmak — Thu, 21 Jun 2018 03:14:13 GMT

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

Re: ODS Excel , Yea5. date format

Reeza — Thu, 21 Jun 2018 14:52:27 GMT

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

Re: ODS Excel , Yea5. date format

ballardw — Thu, 21 Jun 2018 15:25:35 GMT

@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...