BookmarkSubscribeRSS Feed
renjithr
Quartz | Level 8

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.

10 REPLIES 10
Astounding
PROC Star

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?

renjithr
Quartz | Level 8

Group variables should be RACE+GROUP+DATE. GROUP and DATE  are always constant for a single value of RACE.

 

Length of LOCATION IS 10.

Astounding
PROC Star

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;

renjithr
Quartz | Level 8

Thanks. 

Just curious ..if the GROUP  and DATE are not constant,then what will be the code change?

Astounding
PROC Star

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.

renjithr
Quartz | Level 8

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.

Astounding
PROC Star

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.

renjithr
Quartz | Level 8

Thank you!

MikeZdeb
Rhodochrosite | Level 12

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

 

ballardw
Super User

@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.

sas-innovate-2024.png

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.

 

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
  • 10 replies
  • 2425 views
  • 1 like
  • 4 in conversation