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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.