BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
genemroz
Quartz | Level 8

Esteemed Advisers:

I suspect this is a simple problem in summing but the solution is eluding me:

I need help with SAS code that sums k(binary) for Types A1, B1 and C1 by ID and outputs as ID, D1,sumk and similarly summing for Types A2,B2,C2 to produce ID, D2, sumk , then summing for Types A3,B3, C3 to produce ID,D3,sumk and so forth. See below:

Data Have;
input ID Type $ K;

datalines;
1 A1 0
1 A2 0
1 A3 1
1 B1 1
1 B2 0
1 B3 0
1 C1 0
1 C2 0
1 C3 0
2 A1 0
2 A2 0
2 A3 1
2 B1 0
2 B2 0
2 B3 0
2 C1 0
2 C2 1
2 C3 0
.
.
.
;


data want;
ID Type $ sumk;
1 D1 1
1 D2 0
1 D3 1
2 D1 0
2 D2 1
2 D3 1
.
.
.
;

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Hello @genemroz,

 

Try PROC SQL:

proc sql;
create table want as
select id, cats('D',substr(type,2)) as type length=8, sum(k) as sumk
from have
group by 1,2
order by 1,2;
quit;

View solution in original post

12 REPLIES 12
FreelanceReinh
Jade | Level 19

Hello @genemroz,

 

Try PROC SQL:

proc sql;
create table want as
select id, cats('D',substr(type,2)) as type length=8, sum(k) as sumk
from have
group by 1,2
order by 1,2;
quit;
genemroz
Quartz | Level 8
Thank you for the prompt response and excellent solution using PROC SQL. However, I am curious to know if the same result can be obtained via a SAS Data Step?
Thanks again...

Gene
PaigeMiller
Diamond | Level 26

@genemroz wrote:
Thank you for the prompt response and excellent solution using PROC SQL. However, I am curious to know if the same result can be obtained via a SAS Data Step?


You shouldn't write your own DATA step code to sum by groups. SAS has already put in the effort to provide ways to sum by groups (PROC SUMMARY, PROC MEANS, PROC SQL) and has tested this code, debugged the code, and this code has been proven in a bazillion real-world applications. You (or your company or university) is paying SAS for this code, use it! Don't spend your time to re-invent the wheel.

 

We see many many many times when people try to write their own data step code to do this, they either get it wrong, or spend huge amounts of time on it, neither of which is a good outcome.

--
Paige Miller
genemroz
Quartz | Level 8
Thanks for your wise comments. Having already spent too much time on this before posting the question this morning, I completely understand.

Regards,
Gene
FreelanceReinh
Jade | Level 19

@genemroz wrote:
Thank you for the prompt response and excellent solution using PROC SQL. However, I am curious to know if the same result can be obtained via a SAS Data Step?
Thanks again...

Gene

You're welcome.

 

Here's a DATA step solution using an array:

data want(drop=i k);
array d[3] _temporary_;
do until(last.id);
  set have;
  by id;
  d[input(substr(type,2),32.)]+k;
end;
do i=1 to dim(d);
  type=cats('D',i);
  sumk=d[i];
  output;
end;
call missing(of d[*]);
run;

This assumes that HAVE is sorted (or indexed) by ID.

genemroz
Quartz | Level 8
Thanks for this. I look forward to trying it out on my dataset.

Gene
Tom
Super User Tom
Super User

Do you need a dataset? Or just a report?

proc format ;
value $d 
  'A1','B1','C1'='D1'
  'A2','B2','C2'='D2'
  'A3','B3','C3'='D3'
;
run;

proc report data=have ;
 column id type k ;
 define id / group ;
 define type / group format=$d. ;
 define k / sum;
run;

image.png

genemroz
Quartz | Level 8
I need the result as a dataset, not a report.

Thanks,

Gene
PaigeMiller
Diamond | Level 26

@genemroz wrote:
I need the result as a dataset, not a report.


proc format ;
value $d 
  'A1','B1','C1'='D1'
  'A2','B2','C2'='D2'
  'A3','B3','C3'='D3'
;
run;

proc summary nway data=have;
    class id type;
    var k;
    format type $d.;
    output out=want sum=;
run;
--
Paige Miller
genemroz
Quartz | Level 8
Thanks for the prompt response and another excellent solution!

Gene
Ksharp
Super User

Just for fun.

Data Have;
input ID Type $ K;
datalines;
1 A1 0
1 A2 0
1 A3 1
1 B1 1
1 B2 0
1 B3 0
1 C1 0
1 C2 0
1 C3 0
2 A1 0
2 A2 0
2 A3 1
2 B1 0
2 B2 0
2 B3 0
2 C1 0
2 C2 1
2 C3 0
;

proc sql;
create table want as
select id,'D1' as type,sum(k) as sum from have where type in ('A1','B1','C1') group by id 
union
select id,'D2' as type,sum(k) as sum from have where type in ('A2','B2','C2') group by id 
union
select id,'D3' as type,sum(k) as sum from have where type in ('A3','B3','C3') group by id 
;
quit;
genemroz
Quartz | Level 8
Thanks for this alternative!

Regards,

Gene

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 1428 views
  • 7 likes
  • 5 in conversation