Help using Base SAS procedures

Getting column labels to repeat across rows using proc tabulate

Reply
N/A
Posts: 0

Getting column labels to repeat across rows using proc tabulate

Dear Colleagues,

I use the following code to produce a table that is fine except that I need the column formatting in the first column to repeat across all rows generated by the class variable in the second column.

***THE CODE****

proc tabulate data=x noseps missing out=x_for_report;
class delq_n delq_nplus1 dpe chgoff pdoff product_desc/preloadfmt exclusive;
var num_rec;
table product_desc='Product' all='Total',
delq_n='DPD MTH N'*delq_nplus1='DPD MTH N+1' all,
(dpe='Month' all)*num_rec=''*f=comma12./rts=25 row=float condense
box='Markov Count' printmiss misstext='0';
keylabel ALL='Total' pctn='Pct' n='Number' colpctn='Col Pct' rowpctsum='Row Pct' sum='Num';
format dpe monyy. delq_n $delq_n. delq_nplus1 $delq_nplus.;
run;

**** END CODE****

The table generates:
Month Total
09-Dec 10-Jan 10-Feb 10-Mar 10-Apr
Num Num Num Num Num Num
DPD MTH N DPD MTH N+1 9,814 10,107 10,738 axxDPD_0 axxDPD_0
b001-009 3 0 5 1 6 5
c010-029 9 1 0 6 0 76
d030-059 10 2 8 3 2 25
e060-089 0 0 0 0 0 0
f090-119 0 0 0 0 0 0
g120-149 0 0 0 0 0 0
hxxx150+ 0 0 0 0 0 0
iPdOff 5 7 6 6 7 11
jChgOff 1 0 0 0 0 1


However, I need the "axxDPD_0" to be generated in the first column of each row.

How can this be accomplished?

Thank you for your guidance,
Teresa
SAS Super FREQ
Posts: 8,864

Re: Getting column labels to repeat across rows using proc tabulate

Posted in reply to deleted_user
Hi:
I am having a hard time understanding what axxDPD_0 is and where it's coming from. Your posted output shows it at the end of a row and that doesn't look right given the structure of your table.

You have PRODUCT in the PAGE dimension, which would normally put the PRODUCT sitting on top of the "box area". In your case, you have the box labeled with the string "Markov Count", which i do NOT see in your output at all. Where is the label "Markov Count" in your posted output???

Then you have DELQ_N and DELQ_NPLUS1 in the ROW dimension and then you have DPE and NUM_REC in the COLUMN dimension (for simplicity, I am not mentioning the ALL or the statistics you have in the dimensions). So, what variable has a value of axxDPD_0??? PRODUCT???

You also have OUT= in the TABULATE code
[pre]
out=x_for_report
[/pre]

if you do a PROC PRINT on X_FOR_REPORT dataset, do you see the values you expect????

To get a better idea of what you might want, I have this simplified TABULATE code for just a few months and a few products using SASHELP.PRDSALE
[pre]
ods listing;

proc tabulate data=sashelp.prdsale formchar='|-+++|-++++';
where quarter = 1 and product in ('DESK', 'TABLE');
class product country region month;
table product all,
country*region all,
(month all)*n / box='Markov Count';
run;

[/pre]

this is the output for 2 PRODUCTS: DESK and TABLE (ALL table not shown)
[pre]
Product DESK
+-----------------------------------+--------------------------------------+------------+
|Markov Count | Month | |
| |------------+------------+------------+ |
| | Jan | Feb | Mar | All |
| |---------------------------------------------------+
| | N | N | N | N |
|-----------------+---------------------------------------------------------------------+
|Country |Region | | | | |
|-----------------------------------+ | | | |
|CANADA |EAST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------+
| |WEST | 4.00| 4.00| 4.00| 12.00|
|---------------------------------------------------------------------------------------+
|GERMANY |EAST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------+
| |WEST | 4.00| 4.00| 4.00| 12.00|
|---------------------------------------------------------------------------------------+
|U.S.A. |EAST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------+
| |WEST | 4.00| 4.00| 4.00| 12.00|
|-----------------+---------------------------------------------------------------------+
|All | 24.00| 24.00| 24.00| 72.00|
+-----------------------------------+------------+------------+------------+------------+




Product TABLE
+-----------------------------------+--------------------------------------+------------+
|Markov Count | Month | |
| |------------+------------+------------+ |
| | Jan | Feb | Mar | All |
| |---------------------------------------------------+
| | N | N | N | N |
|-----------------+---------------------------------------------------------------------+
|Country |Region | | | | |
|-----------------------------------+ | | | |
|CANADA |EAST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------+
| |WEST | 4.00| 4.00| 4.00| 12.00|
|---------------------------------------------------------------------------------------+
|GERMANY |EAST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------+
| |WEST | 4.00| 4.00| 4.00| 12.00|
|---------------------------------------------------------------------------------------+
|U.S.A. |EAST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------+
| |WEST | 4.00| 4.00| 4.00| 12.00|
|-----------------+---------------------------------------------------------------------+
|All | 24.00| 24.00| 24.00| 72.00|
+-----------------------------------+------------+------------+------------+------------+
[/pre]

Can you restate your question in the context of the above tables -- do you want to see the CANADA, GERMANY and USA on every row for region? Or do you want to see the PRODUCT values of DESK and TABLE someplace other than on top of the BOX area??? (Note that I did not use any analysis variables, in my table, since your question seems to be about the values shown for the CLASS variables, not the numbers used for analysis.)

PROC TABULATE creates output by "spanning" rows and "spanning" columns when you do nesting of class variables (as with the * operator). So TABULATE will NOT put a value on every row -- that is inherent in how TABULATE operates. And, when you use the PAGE dimension, the PAGE dimensioin causes TABULATE to make a table for every unique value of the PAGE variable -- in order to identify the PAGE dimension separate from the ROW and COL dimensions,

Without PRODUCT and ALL in the page dimension, you could generate a TABLE like this from PROC TABULATE:
[pre]
+-----------------------------------+--------------------------------------+------------+
|Markov Count | Month | |
| |------------+------------+------------+ |
| | Jan | Feb | Mar | All |
| |---------------------------------------------------+
| | N | N | N | N |
|-----------+-----------+---------------------------------------------------------------+
|Product |Country |Region | | | | |
|-----------------------------------+ | | | |
|DESK |CANADA |EAST | 4.00| 4.00| 4.00| 12.00|
| | |---------------------------------------------------------------+
| | |WEST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------------+
| |GERMANY |EAST | 4.00| 4.00| 4.00| 12.00|
| | |---------------------------------------------------------------+
| | |WEST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------------+
| |U.S.A. |EAST | 4.00| 4.00| 4.00| 12.00|
| | |---------------------------------------------------------------+
| | |WEST | 4.00| 4.00| 4.00| 12.00|
|---------------------------------------------------------------------------------------+
|TABLE |CANADA |EAST | 4.00| 4.00| 4.00| 12.00|
| | |---------------------------------------------------------------+
| | |WEST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------------+
| |GERMANY |EAST | 4.00| 4.00| 4.00| 12.00|
| | |---------------------------------------------------------------+
| | |WEST | 4.00| 4.00| 4.00| 12.00|
| |---------------------------------------------------------------------------+
| |U.S.A. |EAST | 4.00| 4.00| 4.00| 12.00|
| | |---------------------------------------------------------------+
| | |WEST | 4.00| 4.00| 4.00| 12.00|
|---------------------------------------------------------------------------------------+
|All |CANADA |EAST | 8.00| 8.00| 8.00| 24.00|
| | |---------------------------------------------------------------+
| | |WEST | 8.00| 8.00| 8.00| 24.00|
| |---------------------------------------------------------------------------+
| |GERMANY |EAST | 8.00| 8.00| 8.00| 24.00|
| | |---------------------------------------------------------------+
| | |WEST | 8.00| 8.00| 8.00| 24.00|
| |---------------------------------------------------------------------------+
| |U.S.A. |EAST | 8.00| 8.00| 8.00| 24.00|
| | |---------------------------------------------------------------+
| | |WEST | 8.00| 8.00| 8.00| 24.00|
|-----------+-----------+---------------------------------------------------------------+
|All | 48.00| 48.00| 48.00| 144.00|
+-----------------------------------+------------+------------+------------+------------+
[/pre]

That might get you closer to what you want.

cynthia

PS -- to preserve indenting when you cut and paste code and output, the following post might prove useful:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

Use the [pre] and [/pre] tags around your code and output in order to maintain indenting and spacing.
N/A
Posts: 0

Re: Getting column labels to repeat across rows using proc tabulate

Posted in reply to Cynthia_sas
Thank you for taking the time to illustrate the problem correctly. In fact I require Canada, Germany etc. to appear in every row generated by region.

Thanks again,
Teresa
SAS Super FREQ
Posts: 8,864

Re: Getting column labels to repeat across rows using proc tabulate

Posted in reply to deleted_user
Hi:
As I said, PROC TABULATE will not do that for you (fill in the rows) -- that's why it has spanning row header values -- so you know which class value belongs to which rows of calculated statistics.If you use ODS HTML, ODS RTF or ODS PDF as your destination, the output looks very nice from TABULATE with the SPANNING headers. When you are sending output to the LISTING destination, if you use the NOSEPS option, you are removing the internal "lines" and it would be hard to see the spanning anymore. What is your final destination for the output file?? Do you want LISTING output or do you want HTML or RTF or PDF???

You might need to look at your X_FOR_REPORT file to see whether you can transpose it or otherwise use it with PROC REPORT or the DATA step in order to generate the report you want.

When you look in X_FOR_REPORT dataset, you will see a variable called _TYPE_. _TYPE_ is a special variable that shows you which class variables contributed to the summarized observation in the dataset. You will have many different _TYPE_ variable values. It will be a character variable which contains 0 and 1 to show which CLASS variable contributed to the statistic -- you'll have to figure out which _TYPE_ variable contains the information you want.

For example if you have this CLASS statement:
CLASS VAR1 VAR2 VAR3;

Then a _TYPE_ of '111' means that all 3 CLASS Variable values were used for that summarized observation. But, if you have a _TYPE_ of '110' if means that only VAR1 and VAR2 contributed to the summarized observation. And _TYPE_ of '101' means that only VAR1 and VAR3 contributed, etc, etc.

cynthia
N/A
Posts: 0

Re: Getting column labels to repeat across rows using proc tabulate

Posted in reply to Cynthia_sas
Thank you Cynthia. I suspected that I would have to pursue a Proc Report solution.
Ask a Question
Discussion stats
  • 4 replies
  • 666 views
  • 0 likes
  • 2 in conversation