Help using Base SAS procedures

Proc Tabulate customize order

Reply
Occasional Contributor KG2
Occasional Contributor
Posts: 15

Proc Tabulate customize order

Current Output.pngdesired output.png

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;
Trusted Advisor
Posts: 1,444

Re: Proc Tabulate customize order

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.

Super User
Posts: 10,890

Re: Proc Tabulate customize order

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.

 

Occasional Contributor KG2
Occasional Contributor
Posts: 15

Re: Proc Tabulate customize order

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

 

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

 

Super User
Posts: 9,782

Re: Proc Tabulate customize order

Do not forget CLASSDATA= option of PROC TABULATE .


Occasional Contributor KG2
Occasional Contributor
Posts: 15

Re: Proc Tabulate customize order

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

 

Super User
Posts: 10,890

Re: Proc Tabulate customize order

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;
Super User
Posts: 9,782

Re: Proc Tabulate customize order

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;

Ask a Question
Discussion stats
  • 7 replies
  • 119 views
  • 0 likes
  • 4 in conversation