topic Re: add additional row for totals in data step in SAS Programming

add additional row for totals in data step

DanD999 — Thu, 30 Apr 2015 18:55:28 GMT

I have two questions. Question one is if there is a better way to add a totals row to the data. I originally wanted to do it in a data step but couldn't figure out how to do it. Question two is about the last data step. When I put the totals1 and detail1 datasets together, originally I had detail1 listed first like "set detail1 totals1;" but when I did that it kept dropping the "a" in the sort column for the rows in the totals dataset. I thought it was because in the detail1 dataset that came first the sort column was only 1 character so it made the sort column 1 character which cut off the "a" when it started processing the totals1 dataset. So, I tried using the "length" statement but it didn't make any difference. I finally listed the totals1 dataset first and it worked. How could I have done it so that the "a" wasn't dropped?

data have;
input @1 h_id 1. @3 p_code $5. @9 a_u 1. @11 r_u 1. @14 start_date mmddyy10. @24 end_date mmddyy10. ;
format start_date end_date mmddyy10.;
cards;
1 J5563 2 4 03/17/2014 03/18/2014
2 J3363 4 2 03/18/2014 03/19/2014
1 J4789 2 6 03/19/2014 03/24/2014
3 J8434 2 2 03/20/2014 03/24/2014
3 J4444 5 5 03/21/2014 03/24/2014
1 J5563 3 1 03/24/2014 03/27/2014
;
run;

proc sql;
create table detail as
select *
from have
;
run;

proc sql;
create table totals as
select
h_id,
min(start_date) as start_date length=4 format=mmddyy10.,
max(end_date) as end_date length=4 format=mmddyy10.,
sum(a_u) as a_u,
sum(r_u) as r_u
from detail
group by h_id
;
run;

data totals1;
set totals;
/* length sort $2;*/
p_code='636';
sort=put(h_id,9.)||'a';
run;

data detail1;
set detail;
/* length sort $2;*/
sort=put(h_id,9.);
run;

data combined;
set totals1 detail1;
/* length sort $2;*/
put _all_;
run;

proc sql;
create table combined1 as
select *
from combined
order by sort
;
run;

Thanks

Re: add additional row for totals in data step

ballardw — Thu, 30 Apr 2015 19:21:06 GMT

This line sort=put(h_id,9.); creates a variable that is 9 characters long. When the value of h_id is one digit you have 8 leading spaces.
sort=put(h_id,9.)||'a'; creates a variable 10 characters long, same as above but adds one for the 'a'.

this will address the specific issue. You want the overriding LENGTH statement before the set so everything gets brought into the data with that property.

data combined;

length sort $ 10 ;

set detail1 totals1;

put _all_;

run;

If you don't want the leading blanks use:

sort = strip(put(h_id,9.));

and

sort=cats(put(h_id,9.),'a'); or ignore the put and just use h_id which will be converted to character using the BEST format.

Re: add additional row for totals in data step

Steelers_In_DC — Thu, 30 Apr 2015 19:37:35 GMT

for the length issue this will fix it:

data combined;

length sort $10.;

set detail1 totals1;

put _all_;

run;

Re: add additional row for totals in data step

Steelers_In_DC — Thu, 30 Apr 2015 19:40:25 GMT

for the total question I'm not sure I understand what you are looking for, try this and see if this is what you want. Sum without group by:

proc sql;

create table combined1 as

select *,sum(a_u) as sum_au,sum(r_u) as sum_ru

from combined

order by sort

;

run;

Re: add additional row for totals in data step

sbb — Thu, 30 Apr 2015 19:41:26 GMT

Consider SAS PROC SUMMARY if you must add total (by GROUP var) row - automatically generated for you, and it can handle SUM, MIN, MAX as you are doing.

After summarization, look at sorting by _TYPE_ in descending order, filtering out _TYPE_=0 row.

Re: add additional row for totals in data step

DanD999 — Thu, 30 Apr 2015 20:25:03 GMT

Mark Johnson wrote:

for the total question I'm not sure I understand what you are looking for, try this and see if this is what you want. Sum without group by:

proc sql;

create table combined1 as

select *,sum(a_u) as sum_au,sum(r_u) as sum_ru

from combined

order by sort

;

run;

I think you did misunderstand me. If you run my code, it produces the result that I want. I was wondering if there is a better or more efficient way to get to the same result. Specifically I was wondering if it's possible to do it all in a data step. Thanks for the reply.

Re: add additional row for totals in data step

DanD999 — Thu, 30 Apr 2015 20:26:25 GMT

Mark Johnson wrote:

for the length issue this will fix it:
data combined;
length sort $10.;
set detail1 totals1;
put _all_;
run;

Thanks.