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
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;
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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.