Counting Data in proc summary of other output

[Q1983]

data exc_cnt;

set loan7;

incl= coalesce(APP101,AST102);

ln_cnt = 1;

if incl ne '';

keep ln_no prog_ctgy gross_ln_amt ln_cnt incl

run;

Sample Output

Ln_no       prog_ctgy   gross_ln_amt    incl

11111           Doctor      100            101

11122           Doctor      500           102

11133           PVW         100             101

11444           PVM         500             102

I used a proc summary as such

proc summary data = exc_cnt /*nway*/;               

     class incl ;             

     output out = loan7_summ_exc_cd            

           sum(GROSS_LN_AMT) = Dollars

           sum(ln_cnt) = Units;           

run;

I get something like this (I removed the _TYPE_ and _FREQ_)

Incl      Dollars           UNITS

           1200               4

101       200                2

102       1000               2

I need the output to incorporate the prog_ctgy also for something like

         Doctor                         PVM

           Units  dollars        Units  dollars

101      1          100              1          100

102     1           500            1           500

Also note that incl returns a numeric so I want to transfer to a character and rename it (ie 101 would become Appraisal_101)

I am wondering if a proc means would be better or need information on how to get the desired output

[Reeza]

As long as you don't need distinct counts you can use proc tabulate.

[ballardw · Accepted solution]

Are you looking for a data set to further manipulate or a report?

If you had prog_ctgy on your class statement (and NWAY may make more sense or the TYPES option ) then you would have your data in rows and then you could transpose (if actually needed).

If a report then send straight to a report procedure:

proc tabulate data=exc_cnt;

   class prog_ctgy incl;

   var gros_ln_amt ln_ctn;

   table incl,

            prog_ctgy*(Ln_cnt=''*sum='Units' gros_ln_amt=''*sum='Dollars')

    ;

run;

[Q1983]

proc tabulate data=exc_cnt ;

class prog_ctgy codedesc;

var gross_ln_amt ln_cnt;

table codedesc all,

prog_ctgy*(Ln_cnt=''*sum='Exceptions Units' gross_ln_amt=''*sum='ExceptionsDollars' n*f=8. colpctn)

;

run;

Thanks for the advise.  I used the above code to create the proc tab enclosed in attachment.  Here are my questions

1. There can be multiple codedesc for each entry.  Would I need to do a proc transpose and list each codedesc, then replace codedesc with the individual names of each variable to avoid double counting?

2. How would I suppress the N in each section.  I did some research and did not find anything

[Q1983]

Cancel question #I think I have that one figured out.  just attempting to suppress the  N

[ballardw]

If you do not explicitly ask for N and there is another statistic requested you should not get an N value.