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
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;
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;
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.
Thanks, Ballardw! I needed a dataset.
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.