topic Re: Counting Data in proc summary of other output in SAS Programming

Counting Data in proc summary of other output

Q1983 — Mon, 24 Oct 2016 18:10:25 GMT

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

Re: Counting Data in proc summary of other output

Reeza — Mon, 24 Oct 2016 18:36:17 GMT

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

Re: Counting Data in proc summary of other output

ballardw — Mon, 24 Oct 2016 20:06:43 GMT

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;

Re: Counting Data in proc summary of other output

Q1983 — Tue, 25 Oct 2016 16:47:30 GMT

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

Re: Counting Data in proc summary of other output

Q1983 — Tue, 25 Oct 2016 18:25:49 GMT

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

Re: Counting Data in proc summary of other output

ballardw — Thu, 27 Oct 2016 19:14:51 GMT

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