How to keep a row on the top of each group

[lizzy28]

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:

questionid	question	answer	count	percent
1	Age	TOTAL	981	1
1	Age	0-64	29	0.029562
1	Age	65-74	766	0.780836
1	Age	75-84	161	0.164118
1	Age	85+	25	0.025484
2	What's your marital status and current living situation?: Marital Status	TOTAL	594	1
2	What's your marital status and current living situation?: Marital Status	DIVORCED	63	0.106061
2	What's your marital status and current living situation?: Marital Status	DOMESTIC PARTNER	14	0.023569
2	What's your marital status and current living situation?: Marital Status	MARRIED	393	0.661616
2	What's your marital status and current living situation?: Marital Status	SEPARATED	3	0.005051
2	What's your marital status and current living situation?: Marital Status	SINGLE	42	0.070707
2	What's your marital status and current living situation?: Marital Status	WIDOWED	79	0.132997
3	What's the annual gross income for your household?	TOTAL	561	1
3	What's the annual gross income for your household?	$0.00 - $18,000	29	0.051693
3	What's the annual gross income for your household?	$18,001 - $50,000	194	0.345811
3	What's the annual gross income for your household?	$50,001 OR MORE	338	0.602496

Can anyone help me out?

Thanks a lot!

Lizi

[Astounding · Accepted solution]

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;

answer='TOTAL';

output;

do until (last.questionid);

   set have;

   by questionid;

   output;

end;

drop total_count;

run;

[ballardw]

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.

[lizzy28]

Thanks, Ballardw! I needed a dataset.