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
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;
As long as you don't need distinct counts you can use proc tabulate.
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;
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
Cancel question #I think I have that one figured out. just attempting to suppress the N
If you do not explicitly ask for N and there is another statistic requested you should not get an N value.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.