BookmarkSubscribeRSS Feed
Ali_jarmak
Calcite | Level 5

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;
6 REPLIES 6
Reeza
Super User

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;

 

Ali_jarmak
Calcite | Level 5

Thank you for your prompt reply.

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

 

ALi

Reeza
Super User

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. 

 

Ali_jarmak
Calcite | Level 5

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

Reeza
Super User

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


 

ballardw
Super User

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 937 views
  • 0 likes
  • 3 in conversation