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;
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;
Thank you for your prompt reply.
I have tried year4. as you have suggested. and is giving the same issue.
ALi
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.
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
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
@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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.