BookmarkSubscribeRSS Feed
omega1983
Calcite | Level 5

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

5 REPLIES 5
art297
Opal | Level 21

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
Calcite | Level 5

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
Amethyst | Level 16

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

Thanks,
Jag
omega1983
Calcite | Level 5

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
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 5055 views
  • 2 likes
  • 3 in conversation