topic Re: Format by Year ODS Excel in ODS and Base Reporting

Format by Year ODS Excel

Q1983 — Wed, 24 Jul 2019 21:51:49 GMT

data have;

input name $ Date1 Date9.;

format date1 mmddyy10.;

datalines;

Joe 12Dec2018

Sam 4Jan2018

Tim 11Jan2018

Mic 22Dec2018

Jim 12Jun2019

;run;

OPTIONS orientation=landscape papersize="letter" missing=' ';

options nocenter ls=145 ps=60 orientation=landscape nomprint nosymbolgen

FMTSEARCH=(datamart) nosource2 compress=yes reuse=yes;

%Let ReportName = Have_;

%Let ReportOut = &ReportName.&rptdate;

%LET VERSION = V1;

%Let FNote1=Confidential and Proprietary Information.;

%Let FNote2=Property of SunTrust For Internal Use Only.;

%put REPORTNAME=&ReportName;

%put REPORTOUT=&ReportOut;

ods listing close;

options missing=' ' topmargin=0.30in bottommargin=0.01in rightmargin=0.25in leftmargin=0.25in;

ODS TAGSETS.ExcelXP file="&ReportOut..xml" Path="&OutDir" style=&dors_style;

%CoverSheet(&ReportName,&pbd_date_key,&Version);

ODS TAGSETs.ExcelXP

options(Zoom='90' Frozen_Headers='No' Frozen_RowHeaders='No' Autofit_Height = 'YES'

center_horizontal='YES' Embedded_Footnotes='YES' Embedded_Titles="NO");

ODS TAGSETs.ExcelXP options(sheet_name = "Have" sheet_interval= 'none');

PROC REPORT = have NOWD split='*' wrap;

Columns _all_;

RUN;

When I view the report it formats based on the date in ascending order. However is there something I can do to cause this to order by Year, then month, then date at runtime or display time when its exported?

Something like this

2018

Jan

etc...........

Re: Format by Year ODS Excel

ballardw — Thu, 25 Jul 2019 15:18:50 GMT

@Q1983 wrote:

data have;

input name $ Date1 Date9.;

format date1 mmddyy10.;

datalines;

Joe 12Dec2018

Sam 4Jan2018

Tim 11Jan2018

Mic 22Dec2018

Jim 12Jun2019

;run;

OPTIONS orientation=landscape papersize="letter" missing=' ';

options nocenter ls=145 ps=60 orientation=landscape nomprint nosymbolgen

FMTSEARCH=(datamart) nosource2 compress=yes reuse=yes;

%Let ReportName = Have_;

%Let ReportOut = &ReportName.&rptdate;

%LET VERSION = V1;

%Let FNote1=Confidential and Proprietary Information.;

%Let FNote2=Property of SunTrust For Internal Use Only.;

%put REPORTNAME=&ReportName;

%put REPORTOUT=&ReportOut;

ods listing close;

options missing=' ' topmargin=0.30in bottommargin=0.01in rightmargin=0.25in leftmargin=0.25in;

　

ODS TAGSETS.ExcelXP file="&ReportOut..xml" Path="&OutDir" style=&dors_style;

　

%CoverSheet(&ReportName,&pbd_date_key,&Version);

ODS TAGSETs.ExcelXP

options(Zoom='90' Frozen_Headers='No' Frozen_RowHeaders='No' Autofit_Height = 'YES'

center_horizontal='YES' Embedded_Footnotes='YES' Embedded_Titles="NO");

ODS TAGSETs.ExcelXP options(sheet_name = "Have" sheet_interval= 'none');

PROC REPORT = have NOWD split='*' wrap;

Columns _all_;

RUN;

When I view the report it formats based on the date in ascending order. However is there something I can do to cause this to order by Year, then month, then date at runtime or display time when its exported?

Something like this

2018

   Jan

     4

   11

etc...........

I think you need to show a bit more of what you actually expect the output to look like as I can't decipher your example, especially since it doesn't show anything but parts of dates.

The code you show doesn't run , missing data= on the proc report, so I don't know what you mean by "in date order" . Adding a data= does not make the report run in ascending date order unless you are doing something to the data you do not show or are setting options somewhere not shown such as an ORDER option for one of the variables.

Or are you asking about changing the format of the date variable? If you want something like 2018 Jan 4 then you need a custom format. Maybe as follows and set that as the display format for the date variable. Maybe.

proc format library=work;
picture ymond   
low-high ='%Y%3B%e'  (datatype=date)
;
run;

Re: Format by Year ODS Excel

Reeza — Wed, 24 Jul 2019 22:32:06 GMT

Not easily, but it can be done. You'd have to add dummy variables to do that and then format them according to how you'd like to view the results. The automatic reporting tools like VA and Tableau have more options for that sort of behaviour.

EDIT: dummy variables are not dummy coded,ie categorical, instead you'll need duplicates of the date to use. PROC REPORT allows alias so it may be possible just using the one, but I'm not aware of how and would be curious to see if it's possible. @Cynthia_sas is likely the best for this question.

Re: Format by Year ODS Excel

Cynthia_sas — Thu, 25 Jul 2019 14:18:00 GMT

Thanks for the vote of confidence, Reeza! I can think of a couple of things. The issue is that without better data or a better understanding of what the output should look like, it's hard to visualize what is needed. There are only 2 variables in the posted data -- hardly enough to need a landscape orientation. It's not clear whether making new variables for year, month and day would be the best approach so there are 3 columns available for ordering or grouping or whether a computed column or alias would work. It really depends on what the ultimate report needs to look like and whether Year, Month and Day need to behave like display items or order/group items.

Cynthia