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 |
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;
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;
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.
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!
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.