DATA Step, Macro, Functions and more

Counting Data in proc summary of other output

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 141
Accepted Solution

Counting Data in proc summary of other output

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


Accepted Solutions
Solution
‎10-24-2016 04:20 PM
Super User
Posts: 11,343

Re: Counting Data in proc summary of other output

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;

View solution in original post


All Replies
Super User
Posts: 19,791

Re: Counting Data in proc summary of other output

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

Solution
‎10-24-2016 04:20 PM
Super User
Posts: 11,343

Re: Counting Data in proc summary of other output

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;

Frequent Contributor
Posts: 141

Re: Counting Data in proc summary of other output

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

Frequent Contributor
Posts: 141

Re: Counting Data in proc summary of other output

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

Super User
Posts: 11,343

Re: Counting Data in proc summary of other output

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 410 views
  • 2 likes
  • 3 in conversation