Hi,
I have a dataset like below:
Race | Group | Location | Date | Count |
White | All | LA | Aug2016 | 1 |
White | All | ANA | Aug2016 | 2 |
Black | All | LA | Aug2016 | 2 |
Black | All | ANA | Aug2016 | 3 |
I want the dataset to be like this:
Race | Group | Location | Date | Count |
White | All | LA | Aug2016 | 1 |
White | All | ANA | Aug2016 | 2 |
White | All | Region | Aug2016 | 3 |
Black | All | LA | Aug2016 | 2 |
Black | All | ANA | Aug2016 | 3 |
Black | All | Region | Aug2016 | 5 |
Please share your thoughts.
So what constitutes a group where you want subtotals? Just RACE? RACE + one of the other variables? Are GROUP and DATE always constant for a single value of RACE? What is the length of the existing variable LOCATION?
Group variables should be RACE+GROUP+DATE. GROUP and DATE are always constant for a single value of RACE.
Length of LOCATION IS 10.
OK, let's try it this way then:
data want;
set have;
by race notsorted;
if first.race then totcount = count;
else totcount + count;
output;
if last.race;
count = totcount;
location = 'Region';
output;
drop totcount;
run;
Thanks.
Just curious ..if the GROUP and DATE are not constant,then what will be the code change?
First you have to decide what the result would be. Then we can talk about code changes. If the other variables change, what values belong on the "Region" observations? That's one of the choices you have to make.
Sure.
Here is my new have:
Race | Group | Location | Date | Count |
White | All | LA | Aug-16 | 1 |
White | All | ANA | Aug-16 | 2 |
Black | All | LA | Aug-16 | 2 |
Black | All | ANA | Aug-16 | 3 |
Black | 65P | ANA | Aug-16 |
1
|
Desired output:
Race | Group | Location | Date | Count |
White | All | LA | Aug-16 | 1 |
White | All | ANA | Aug-16 | 2 |
White | All | Region | Aug-16 | 3 |
Black | All | LA | Aug-16 | 2 |
Black | All | ANA | Aug-16 | 3 |
Black | All | Region | Aug-16 | 5 |
Black | 65P | ANA | Aug-16 | 1 |
Black | 65P | Region | 1-Aug | 1 |
Please let me know.
OK, here's a program to match:
data want;
set have;
by race group notsorted;
if first.group then totcount = count;
else totcount + count;
output;
if last.group;
count = totcount;
location = 'Region';
output;
drop totcount;
run;
For this to work, your data has to be grouped: each RACE/GROUP combination should form a set of adjacent observations.
Thank you!
Hi, how about PROC SUMMARY. Only issue is that the value of the variable LOCATION is missing rather than "REGION" (you can fix that in a subsequent data step). Since there is no ascending or descending order of the location variable in your output, you'd have to play games with the values to get the order you show. (e.g. leading spaces) ...
data x;
input race :$5. group :$3. location :$6. date :monyy. count;
format date monyy.;
datalines;
White All LA Aug-16 1
White All ANA Aug-16 2
Black All LA Aug-16 2
Black All ANA Aug-16 3
Black 65P ANA Aug-16 1
;
proc summary data=x;
class race group location date;
var count;
output out=y (drop=_: )sum=;
types race*group*location*date race*group*date;
run;
data y;
set y;
location = ifc(missing(location),'Region',location);
run;
DATA SET: y
race group location date count
Black 65P Region AUG16 1
Black All Region AUG16 5
White All Region AUG16 3
Black 65P ANA AUG16 1
Black All ANA AUG16 3
Black All LA AUG16 2
White All ANA AUG16 2
White All LA AUG16 1
@renjithr wrote:
Hi,
Please share your thoughts.
Don't put subtotals into data generally.
I would do that as part of a final report using a report procedure such as Report or Tabulate.
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.