topic Proc Tabulate customize order in SAS Procedures

Proc Tabulate customize order

KG2 — Mon, 21 Aug 2017 16:20:28 GMT

Hi,

I have scowered resources and board posts but can't find something to fit my needs.

I am writing a PROC TABULATE code to print a table with each data set that has an identical structure, irrespective of the data being output.

I have one variable and four classes: Yields (var 'ym_yld_a') by BOPKEYSECTOR > by provinces > by currencies.

I need ALL currencies to be part of the table, even if my output doesnt produce data for a given currency.

Here is my code:

- it is very simple

- i have tried variations in the ORDER= to make all currencies to be printed even with missing values, but no luck.

Does anyone know if this is something can be done?

Ive added two photos: 1) what I currently get, usually. 2) the format I want printed, and populated with whatever data is being derived by the program.

THANKS! I will continue to work on this but any help is tremendous!

PROC TABULATE
DATA=YM.TG_POS_200805_S7;

	FORMAT=BEST12.;
	VAR YM_YLD_A;
	CLASS RTTM_INT /	ORDER=UNFORMATTED ;
	CLASS BOPKEY_SECTOR /	ORDER=UNFORMATTED;
	CLASS province /	ORDER=UNFORMATTED;
	CLASS CURR /	ORDER= UNFORMATTED; 
	
	TABLE 
		/* ROW Statement */
		BOPKEY_SECTOR *province *CURR  ,
		/* COLUMN Statement */
		RTTM_INT *(YM_YLD_A * Sum={LABEL="Sum"} );
		
	;

RUN;

Re: Proc Tabulate customize order

Shmuel — Mon, 21 Aug 2017 16:58:52 GMT

Relating to your info:

I have one variable and four classes ...
 
I need ALL currencies to be part of the table, even if my output doesnt produce data for a given currency.

Crteate a skeleton table with all class variables and all need values (including those that are absent or missing)

then merge it with your data. Add option missing to proc tabulate statement.

If you still have issues, post your new full code and show what are your issues.

Re: Proc Tabulate customize order

ballardw — Mon, 21 Aug 2017 17:07:01 GMT

Without data I am guessing that you want some level of a class variable to appear when there is no data. The basic way to do that with proc tabulate is to have a format for the class variable that includes all of the levels for the variable and use the PRELOADFMT option.

Here is a relatively trivial example:

data have;
   do x= 1,2,4;
   output;
   end;
run;

proc format library=work cntlout=work.cntl;
value x
   1 = '1'
   2 = '2'
   3 = '3'
   4 = '4'
;
run;

proc tabulate data=have;
   class x /preloadfmt ;
   format x x.;
   table x, 
         n
        /printmiss;
run;

for preloadfmt to display you need either order=data or the printmiss option for the table or the class option exclusive.

Re: Proc Tabulate customize order

KG2 — Mon, 21 Aug 2017 17:08:25 GMT

Thank you, I am going to work on that a bit.

I was just adding the preloadfmt actually! Lets hope it works,

Re: Proc Tabulate customize order

Ksharp — Tue, 22 Aug 2017 13:00:28 GMT

Do not forget CLASSDATA= option of PROC TABULATE .

Re: Proc Tabulate customize order

KG2 — Tue, 22 Aug 2017 14:54:49 GMT

Thank you, But I have to ask.

After getting SAS to PROC TABULATE CLASS=CURR, all of them, is it possible to also tell says that I want them in a specific order.

For example (1) CAN

(2) USA

(3) EUR

etc

Currently it will do it alphabetically but it is very important to me that CAN is the first, because it is my underlying. Thanks!

I am experimenting but I am not sure if there is some sort of instatement available in the CLASS = or ORDER=.

Re: Proc Tabulate customize order

ballardw — Tue, 22 Aug 2017 20:57:14 GMT

If you can sort the data before the procedure then ORDER=data on the class may help.

another option is to not use character variables but a numeric variable with a custom format to display the desired text and then either sort the data by the numeric and use order=unformatted.

data have;
   do x= 1,2,4,0;
   output;
   end;
run;

proc format library=work cntlout=work.cntl;
value x   (notsorted)
   1 = 'AUS'
   2 = 'FRA'
   3 = 'DDR'
   4 = 'MEX'
   0 = 'Can'
;
run;

proc tabulate data=have ;
   class x /preloadfmt order=unformatted;
   format x x.;
   table x, 
         n
        /printmiss;
run;

Re: Proc Tabulate customize order

Ksharp — Wed, 23 Aug 2017 12:17:09 GMT

You could PAD some white blank before CAN. Here is an example:


proc tabulate data=sashelp.class;
class sex;
var age;
table sex,age;
run;

data class;
 length sex $ 4;
 set sashelp.class;
 if sex='M' then sex='   M';
run;
proc tabulate data=class;
class sex;
var age;
table sex,age;
run;