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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.