adding up -some- lines in a data set

[geneshackman]

Hi all. I have a data set like this

County Race Data
Albany White 3
Albany Black 2
Albany Asian 5
Albany Pacific Islander 3
Allegany White 1
Allegany Black 6
Allegany Asian 2
Allegany Pacific Islander 4

I want to add together the lines for "Asian" and "Pacific Islander" but leave the rest of the lines as they are. So, the resulting race categories would be White, Black and Asian/Pacific Islander, with the numbers for those last two combined.

Any simple way to do this?

Thanks

Gene

[FreelanceReinh · Accepted solution]

Hi @geneshackman,

I would create a format and use it in a PROC SUMMARY step:

proc format;
value $race
'Asian','Pacific Islander'='Asian/Pacific Islander';
run;

proc summary data=have nway;
class county race / order=data;
format race $race.;
var data;
output out=want(drop=_:) sum=;
run;

[geneshackman]

This seems to be the way. Thanks.

[ballardw]

One way:

data have;
   infile datalines delimiter=',';
   input County :$15. Race :$20. Data;
datalines;
Albany,White,3
Albany,Black,2
Albany,Asian,5
Albany,Pacific Islander,3
Allegany,White,1
Allegany,Black,6
Allegany,Asian,2
Allegany,Pacific Islander,4
;

proc format;
value $comb
'Asian','Pacific Islander'='Asian/Pacific Islander';
run;
/* create a new data set*/
proc summary data=have nway;
   class county race;
   format race $comb.;
   var data;
   output out=want (drop=_:) sum=;
run;
/* some different report tables*/

proc tabulate data=have;
   class county race;
   format race $comb.;
   var data;
   table county,
         race*data
   ;
   table county*race,
         data
   ; 
   table race*(county all='Race total'),
         data
   ;
   table county*(race all='County total') all='Overall total',
        data
   ;
run;

This creates a new data set. "Adding up lines" in data set is a poor description.

A better format would include all of the values but works for your limited example.

Formats are an extremely powerful tool in SAS. Groups of many types can be created from different ranges of the same variable. I have up to a dozen Age related formats that are used in the Report, Analysis or Graphing steps.

The groups created by a format are honored by almost every procedure and can reduce coding associated with creating new variables.