BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

How do you get  totals for each county by type:

Name County Total   Type

site1     A          34         2

site2     B          35         1                

site3     A          100       3         

site4     C          12         3

site5     B          23         3

site6     A          25         2

site7     C          56         1

site8     A          88          2

site9     B          90          1

                 Type1     Type2     Type3

countyA    0             147          100

countyB    125          0               23

countyC    56            0               12

I tried this but it doesn't work. Any help you can give will be much appreciated!

PROC SUMMARY DATA=reported order=data;

     CLASS county type;

     VAR total;

     BY type;

OUTPUT OUT= reportedtype SUM=total;

RUN;

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

Hi:

PROC SUMMARY/MEANS will give you numbers, but not the structure you want (with county going down the rows and type going across the columns). My tendency, to create this type of report is to use PROC TABULATE or PROC REPORT. Given the variables you listed, I would suggest code similar to the code shown below.

cynthia

options missing=0;

ods listing close;

 

ods html file='c:\temp\tab_report.html';

proc tabulate data=reported f=comma9.;

title 'TABULATE';

  class county type;

  var total;

  table county all,

        total*type*sum=' ';

run;

proc report data=reported nowd;

title 'REPORT';

  column county total,type;

  define county/ group;

  define type / across;

  define total / sum;

  rbreak after / summarize;

run;

ods html close;

jcis7
Pyrite | Level 9

Thank you so very much!

Tom
Super User Tom
Super User

You could just use PROC FREQ with WEIGHT statement and then TRANSPOSE the resulting "counts".

proc freq data=have ;

  tables county*type /noprint out=vertical ;

  weight total / zeros;

run;

proc transpose data=vertical prefix=Type out=want (drop=_name_ _label_);

  by county ;

  id type ;

  var count;

run;

proc print data=want;

run;

Obs    County    Type1    Type2    Type3

1       A          0      147      100

2       B        125        0       23

3       C         56        0       12

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
  • 3 replies
  • 841 views
  • 3 likes
  • 3 in conversation