## How to keep a row on the top of each group

Solved
Frequent Contributor
Posts: 110

# How to keep a row on the top of each group

Hi everyone,

I need to productionalize data formatting instead of working on an excel file and are getting some questions. I need to add a row to sum up the counts by group and keep the row on the top of each group. Specifically, below is the sample data I have:

 questionid question answer count 1 Age 0-64 29 1 Age 65-74 766 1 Age 75-84 161 1 Age 85+ 25 2 What's your marital status and current living situation?: Marital Status DIVORCED 63 2 What's your marital status and current living situation?: Marital Status DOMESTIC PARTNER 14 2 What's your marital status and current living situation?: Marital Status MARRIED 393 2 What's your marital status and current living situation?: Marital Status SEPARATED 3 2 What's your marital status and current living situation?: Marital Status SINGLE 42 2 What's your marital status and current living situation?: Marital Status WIDOWED 79 3 What's the annual gross income for your household? \$0.00 - \$18,000 29 3 What's the annual gross income for your household? \$18,001 - \$50,000 194 3 What's the annual gross income for your household? \$50,001 OR MORE 338

I would like to have the format as below:

Can anyone help me out?

Thanks a lot!

Lizi

Accepted Solutions
Solution
‎04-27-2017 08:34 AM
Super User
Posts: 6,626

## Re: How to keep a row on the top of each group

I think this is what you are asking for:

data want;

total_count=0;

do until (last.questionid);

set have;

by questionid;

total_count + count;

end;

count=total_count;

output;

do until (last.questionid);

set have;

by questionid;

output;

end;

drop total_count;

run;

All Replies
Solution
‎04-27-2017 08:34 AM
Super User
Posts: 6,626

## Re: How to keep a row on the top of each group

I think this is what you are asking for:

data want;

total_count=0;

do until (last.questionid);

set have;

by questionid;

total_count + count;

end;

count=total_count;

output;

do until (last.questionid);

set have;

by questionid;

output;

end;

drop total_count;

run;

Super User
Posts: 13,293

## Re: How to keep a row on the top of each group

Do you want a data set or a report? Both proc report and tabulate procedures have the ability to do a total  for a variable though they may work better starting from raw data than something presummarized as you show.

Frequent Contributor
Posts: 110

## Re: How to keep a row on the top of each group

Thanks, Ballardw! I needed a dataset.

☑ This topic is solved.