DATA Step, Macro, Functions and more

Proc Summary with character variables

Reply
Contributor
Posts: 59

Proc Summary with character variables

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

PROC Star
Posts: 7,487

Re: Proc Summary with character variables

Posted in reply to omega1983

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;

Contributor
Posts: 59

Re: Proc Summary with character variables

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


Trusted Advisor
Posts: 1,137

Re: Proc Summary with character variables

Posted in reply to omega1983

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 varSmiley Happy;

run;

Please find below the output of the above code

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 59

Re: Proc Summary with character variables

Posted in reply to Jagadishkatam

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

Trusted Advisor
Posts: 1,137

Re: Proc Summary with character variables

Posted in reply to omega1983

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

Thanks,
Jag
Ask a Question
Discussion stats
  • 5 replies
  • 282 views
  • 2 likes
  • 3 in conversation