SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to keep a row on the top of each group

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 93
Accepted Solution

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:

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


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

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;

answer='TOTAL';

output;

do until (last.questionid);

   set have;

   by questionid;

   output;

end;

drop total_count;

run;

View solution in original post


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

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;

answer='TOTAL';

output;

do until (last.questionid);

   set have;

   by questionid;

   output;

end;

drop total_count;

run;

Super User
Posts: 10,483

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: 93

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

Thanks, Ballardw! I needed a dataset.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 131 views
  • 1 like
  • 3 in conversation