Proc Summary with character variables

[omega1983]

I have a dataset that produce following outputfor the variables Type and Category;;

Type                                      Category

Shortsale                                0 to 30

Shortsale                               61-90

Refinance                               31-60

Refinance                               0 to 30

Refinance                              61-90

Modification                           31-60
Modification                           61-90

Modification                           0 to 30

Modification                           31-60

These are character variables and  want to count them in a proc summary like this for desired output

Category                       Shortsale                    Refinance                              Modificatiion

0-30                                   1

31-60                                                                      1

61-90                                 1                                     1  

How can I accomplsh this when the variables are characters.  I am actually counting the instances of the Type and grouping them by the Category

[art297]

You can do that fairly easily with proc freq.  Does the following accomplish what you want (assuming your input file is called 'have')?:

proc freq data=have;

  tables category*type/nocol norow nocum nopercent out=want;

run;

[omega1983]

I need to modify this request.  Let me show the following

Here is the output based on the variable Category;

Category

0 to 30

31 to 60

61 to 90

ShortSales

Modification

0 to 30

31 to 60

61 to 90

My desired output would be

Category                  0 to 30     31 to 60     61 to 90    ShortSales    Modification            Total

0 to 30                        2             1

31 to 60                                                     1

61 to 90

ShortSales               1                                                   1

Modification                             1

So essentially I want to take the vertical count of the variable Category and show it as both the x and y variable and do a count. I also want to do a total at the end

[Jagadishkatam]

Hi ,

Please check the below code, hope this is what you were expecting

data have;

    input type $20. Category & $20.;

cards;

Shortsale                                0 to 30

Shortsale                               61-90

Refinance                               31-60

Refinance                               0 to 30

Refinance                              61-90

Modification                           31-60

Modification                           61-90

Modification                           0 to 30

Modification                           31-60

Shortsale                            ShortSales

Modification                        Modification

;

run;

proc sql;

    create table data as select type, category, count(type) as count from have group by category,type;

quit;

proc transpose data= data out=trans (drop=_name_) prefix=var;

by category;

id type;

run;

data want;

    set trans;

    total=sum(of var:);

run;

Please find below the output of the above code

Thanks,

Jagadish

[omega1983]

This is helpful however how could I apply this if I have an actual proc sql statement that pulls in the type or category.  Your example creates the type from a dataset called have.  I have an actual proc sql

proc sql;

create table pivot1 as

select ln_no,category,count(ln_no) as type

from summary1 

group by category,type;

quit;

Other than that I want the same output you show in your last example

[Jagadishkatam]

yes you may use the proc sql code above, it it requires a modification like below

proc sql;

create table pivot1 as

select ln_no,category,count(ln_no) as type

from summary1 

group by category,ln_no;

quit;

you cannot use type for grouping, as you are generating the count in it. in your code it is not a category.

Thanks,

Jagadish