topic Re: Proc Summary with character variables in SAS Programming

Proc Summary with character variables

omega1983 — Sat, 02 Nov 2013 00:42:40 GMT

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

Re: Proc Summary with character variables

art297 — Sat, 02 Nov 2013 01:12:55 GMT

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;

Re: Proc Summary with character variables

omega1983 — Sat, 02 Nov 2013 03:23:54 GMT

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

Here is the output based on the variable Category;

Re: Proc Summary with character variables

Jagadishkatam — Sat, 02 Nov 2013 11:56:01 GMT

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

Re: Proc Summary with character variables

omega1983 — Sun, 03 Nov 2013 03:22:01 GMT

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

Re: Proc Summary with character variables

Jagadishkatam — Sun, 03 Nov 2013 05:03:16 GMT

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