Sorting in Proc Tabulate

deleted_user — Mon, 08 Sep 2008 17:43:51 GMT

How would I go about sorting by a column in Proc Tabulate? I am using EG supplemented with coding after to produce the report I want. The following is my code:

%macro _SASTASK_DROPDS(dsname);
%IF %SYSFUNC(EXIST(&dsname)) %THEN %DO;
DROP TABLE &dsname;
%END;
%IF %SYSFUNC(EXIST(&dsname, VIEW)) %THEN %DO;
DROP VIEW &dsname;
%END;
%mend _SASTASK_DROPDS;

%LET _EGCHARTWIDTH=0;
%LET _EGCHARTHEIGHT=0;
TITLE;
TITLE1 "Units with Hours where LMP is Greater Than or Equal to $400";
FOOTNOTE;
FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %SYSFUNC(DATE(), EURDFDE9.) at %SYSFUNC(TIME(), TIMEAMPM8.)";
/* -------------------------------------------------------------------
Code generated by SAS Task

Generated on: Monday, September 08, 2008 at 10:00:07 AM
By task: Summary Tables

Input Data: SASUSER.QUERY1_FOR_QUERY1058
Server: SASMain
------------------------------------------------------------------- */

PROC SQL;
%_SASTASK_DROPDS(WORK.SORTTempTableSorted);
QUIT;

/* -------------------------------------------------------------------
Run the tabulate procedure
------------------------------------------------------------------- */
PROC TABULATE
DATA=SASUSER.QUERY1_FOR_QUERY1058
STYLE={FONT_SIZE=1 FONT_FACE='Arial' JUST=CENTER VJUST=BOTTOM} FORMAT=DOLLAR12.2 ORDER=Data

;
VAR CLEARED_PRICE_P COST_LMP_P LOSS_LMP_P CONGESTION_LMP_P;
CLASS Time_Interval_S / ORDER=UNFORMATTED MISSING;
CLASS Scenario_Name / ORDER=UNFORMATTED MISSING;
CLASS Resource_Name / ORDER=UNFORMATTED MISSING;
CLASS Congestion_Name / ORDER=UNFORMATTED MISSING;
TABLE /* Row Dimension */
Congestion_Name={LABEL='Congestion Name' STYLE={FONT_SIZE=2} STYLE(CLASSLEV)={FONT_SIZE=1}}*
Resource_Name={LABEL='Resource Name' STYLE={FONT_SIZE=2} STYLE(CLASSLEV)={FONT_SIZE=1}}*
Time_Interval_S={LABEL='Date/Time' STYLE={FONT_SIZE=2} STYLE(CLASSLEV)={FONT_SIZE=1}},
/* Column Dimension */
Scenario_Name={LABEL='Scenario Name' STYLE={FONT_SIZE=2} STYLE(CLASSLEV)={FONT_SIZE=1}}*(
CLEARED_PRICE_P={LABEL='LMP' STYLE={FONT_SIZE=1}}*
Sum={LABEL='' STYLE={FONT_SIZE=1}}
COST_LMP_P={LABEL='Energy Price' STYLE={FONT_SIZE=1}}*
Sum={LABEL='' STYLE={FONT_SIZE=1}}
LOSS_LMP_P={LABEL='Loss Price' STYLE={FONT_SIZE=1}}*
Sum={LABEL='' STYLE={FONT_SIZE=1}}
CONGESTION_LMP_P={LABEL='Congest. Price' STYLE={FONT_SIZE=1}}*
Sum={LABEL='' STYLE={FONT_SIZE=1}}) ;
;

RUN;
/* -------------------------------------------------------------------
End of task code.
------------------------------------------------------------------- */
RUN; QUIT;
PROC SQL;
%_SASTASK_DROPDS(WORK.SORTTempTableSorted);
QUIT;

TITLE; FOOTNOTE;

>>>>>>

Thanks!

Re: Sorting in Proc Tabulate

deleted_user — Mon, 08 Sep 2008 17:47:01 GMT

oops, that turned out not so readable. Hopefully someone can work with that...

Re: Sorting in Proc Tabulate

deleted_user — Mon, 08 Sep 2008 17:47:34 GMT

Also, I thought I could use ORDER=Data but that did not work for me.

Re: Sorting in Proc Tabulate

Cynthia_sas — Mon, 08 Sep 2008 19:46:12 GMT

Hi:
In some instances, you may need to pre-sort your data so the variables are in the right order BEFORE tabulate starts. You should also read the very long & too wordy to quote here topic in the PROC TABULATE documentation. Look for the topic under this path:
The Tabulate Procedure --> Results: TABULATE Procedure or search on the quoted string:
"Understanding the Order of Headings with ORDER=DATA"
and you will come up with 3 hits -- one on the CLASS statement, one on the PROC TABULATE statement and one on the Results Topic, as described above.

cynthia

Re: Sorting in Proc Tabulate

deleted_user — Mon, 08 Sep 2008 20:17:05 GMT

Thanks Cynthia! I got it to work by presorting the data then ordering the row assignments to ORDER=DATA.

Many thanks!

topic Re: Sorting in Proc Tabulate in ODS and Base Reporting

Sorting in Proc Tabulate

Re: Sorting in Proc Tabulate

Re: Sorting in Proc Tabulate

Re: Sorting in Proc Tabulate

Re: Sorting in Proc Tabulate