BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
Reeza
Super User

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

ballardw
Super User

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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

ballardw
Super User

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

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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