topic Re: Summing across columns based on conditions in SAS Programming

Summing across columns based on conditions

awardell — Fri, 18 Jun 2021 14:33:26 GMT

Hello!

I have a data set that looks as follows:

Say I have a basket with colored balls. I retrieve balls of type x, then record the number retrieved.

I need to first condition on type such that if the type is "Yellow" then I need to sum all the "number_retrievedX" associated with type "Yellow". How would I go about doing this? Thank you so much for your time!

data test; 
input id type1 number_retrieved1 type2 number_retrieved2 type3 number_retrieved3; 
datalines; 
1 Yellow 5 Blue 4 Yellow 7
1 Green 2 Yellow 3 Purple 8
2 Blue 5 Purple 12 Red 10 
3 Yellow 7 Yellow 3 Purple 6
3 Red 4 Yellow 10 Yellow 9
; 
run;

ID	Type1	Number_retrieved1	Type2	Number_retrieved2	Type3	Number_retrieved3	Number_retrieved_total
1	Yellow	5	Blue	4	Yellow	7	12
1	Green	2	Yellow	3	Purple	8	3
2	Blue	5	Purple	12	Red	10	.
3	Yellow	7	Yellow	3	Purple	6	10
3	Red	4	Yellow	10	Yellow	9	19

Re: Summing across columns based on conditions

PaigeMiller — Fri, 18 Jun 2021 14:39:18 GMT

That's great that you provided your data set as SAS data step code.

data test; 
input id type1 $ number_retrieved1 type2 $ number_retrieved2 type3 $ number_retrieved3; 
datalines; 
1 Yellow 5 Blue 4 Yellow 7
1 Green 2 Yellow 3 Purple 8
2 Blue 5 Purple 12 Red 10 
3 Yellow 7 Yellow 3 Purple 6
3 Red 4 Yellow 10 Yellow 9
; 
run; 
data want;
    set test;
    array type type:;
    array nn number_retrieved:;
    sum=0;
    do i=1 to dim(type);
        if type(i)='Yellow' then sum=sum+nn(i);
    end;
    drop i;
run;

Re: Summing across columns based on conditions

tarheel13 — Fri, 18 Jun 2021 22:05:56 GMT

data vertical;
	set test;
	array a1(3) type1-type3;
	array a2(3) number_retrieved1-number_retrieved3;
	do i=1 to dim(a1);
	type=a1(i);
	num=a2(i);
		if not missing(type) then output;
	end;
	
	keep id type num;
run;

proc sql number;
	create table want as select id, sum(num) as count, type
		from vertical
		group by id, type
		order by id;
quit;

proc transpose data=want out=want_t(drop=_name_);
	by id;
	id type;
	var count;
run;

This will get you a dataset that is 1 row per ID with the colors as columns going across the top and the count of each color per ID.