Create table

Reply
Frequent Contributor
Posts: 90

Create table

Hi,

Can you please suggest me how we can create below table.  I can able to create separate table for Tot $10K and Count, but not all together.

Please suggest me and provide some sample example code.Thanks in advance.

2006

2007

2008

Basel LOB Level 2

Tot $10K

Tot Count

Tot $10K

Tot Count

Tot $10K

Tot Count

Corporate Finance

                    145,590

                 4

                     76,345

                     1

                       65,580

                     1

Retail Banking

                  8,832,549

             150

             16,321,198

                245

               70,849,883

                333

Private Banking

                        65,103

                 2

                   134,020

                     3

                 1,530,843

                     9

Card Services

           133,606,328

            3,565

               77,455,887

            2,656

Commercial Banking

               24,071,624

             716

Corporate Center

               35,147,359

               20

             23,515,674

                  23

                 3,956,620

                  27

Total

           68,262,225

         892

        173,653,565

         3,837

         153,858,812

         3,026

Thanks

Super User
Posts: 17,907

Re: Create table

It depends on what your data looks like a lot, but here's a good paper on doing that.

I'd probably use proc tabulate, but proc report would work as well.

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

Frequent Contributor
Posts: 90

Re: Create table

Thanks Reeza !!

.If you can can you please provide me the sample code.

Super User
Posts: 17,907

Re: Create table

Dowload programs

Here's the code from the paper.

If you provide sample data, ie what your data looks, like someone may be able to help with code that's specific to your requirements.

Super Contributor
Posts: 644

Re: Create table

First a word of caution:  the data shown looks real (I hope its not) and if so you should not be posting it outside your company.

My question is whether you want a simple solution that will reproduce the table shown as an Excel worksheet (ie distinct from a PDF or web page)

If so I have often found it easiest to use PROC SUMMARY to summarise your data and then PROC EXPORT it to an otherwise blank but hidden sheet.  Then you can create your report on another sheet and reference the data from the hidden one.  Doing this allows you to use Excel point and click formatting including conditional formatting to your visible table, and add additional titles and totals if desired.  If you need to rearrange the report you just move the cells around with no need to rewrite the SAS code.  And if necessary you can export the report as PDF or paste it into Powerpoint or a document.

Richard in Oz

SAS Super FREQ
Posts: 8,744

Re: Create table

Hi,

There are several different ways to accomplish what the OP wants to do. Since he didn't explain his data  -- whether it was detail data or pre-summarized data or the desired output (report vs table), it is hard to comment on the most appropriate method. However, here is an example of using PROC TABULATE or PROC REPORT with just a bit of conditional highlighting (not all the highlighting, since the criteria for highlighting was not clear to me). The desired output looked like PDF or RTF output to me, but I also used HTML and TAGSETS.EXCELXP for variety of examples.

cynthia

 

proc format;
  value bcksum low-70000='mediumyellow'
             70000<-high = 'white';
  value $bckprd 'BED','CHAIR' = 'lightgreen'
             'DESK','TABLE','SOFA'= 'white';
run;
   
ods rtf file='c:\temp\examp.rtf';
ods html file='c:\temp\examp.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\examp.xml' style=sasweb; 
ods pdf file='c:\temp\examp.pdf' notoc;
 
proc tabulate data=sashelp.prdsale f=comma8.;
  title '1) PROC TABULATE';
  class year product ;
  classlev product / style={background=$bckprd.};
  var actual;
  table product=' ' all='Total',
        actual=' '*year*(sum='Tot 10K'*f=dollar12.*{s={background=bcksum.}} n='Tot Count')
        / box={label='Basel LOB Level 2' s={vjust=b}}
          style={outputwidth=90%};
  keyword all / style={background=white};
run;
    
proc report data=sashelp.prdsale nowd
  style(column)={width=1in};
  title '2) PROC REPORT';
  column product year,actual,(sum n);
  define product / group 'Basel LOB Level 2'
         style(column)={background=$bckprd. width=1.5in}
         style(header)={width=1.5in};
  define year /across ' ';
  define sum / 'Total 10K ' f=dollar12.
         style(column)={background=bcksum.};
  define actual / sum ' ';
  define n / 'Total Count' f=comma8.;
  rbreak after / summarize;
  compute after;
    product = 'Total';
  endcomp;
run;
    
ods _all_ close;

Ask a Question
Discussion stats
  • 5 replies
  • 285 views
  • 0 likes
  • 4 in conversation