Using a do loop to sum, but need to retain missing values in the sum

[awardell]

Hello! 

I have a data set that looks as follows: 

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

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

data test; 
input id type1 number_tranferred1 type2 number_transferred2 type3 number_transferred3; 
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
4 Yellow . "" . "" .
5 Yellow 0 "" . "" . 
6 Yellow 0 Yellow 5 "" . 
; 
run; 

ID	Type1	Number_transferred1	Type2	Number_transferred2	Type3	Number_transferred3	Number_transferred_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
4	Yellow						.
5	Yellow	0					0
6	Yellow	0	Yellow	5			5

I tried code similar to the following; however, using the Number_transferred_total=0 before the do statement, does not allow me to accurately get rows for subject 4 above. I have a 0 filled automatically for everyone, where instead, I need to track those with missing transfers and transfers that actually have a value of 0. 

data want; 
     set test; 
array numbertransferred_array{3} Number_transferred1 Number_transferred2 Number_transferred3;
Number_transferred_total=0;
    do i=1 to dim(numbertransferred_array);
        if numbertransferred_array(i)>=0 then Number_transferred_total=Number_transferred_total+numbertransferred_array(i);
    end;
    drop i;
run; 

Any help would be greatly appreciated. Thank you so much in advance. 

[ballardw · Accepted solution]

First step: Make sure that your example data step code runs. You have character values for the "type" variables but are reading them as numeric.

Second, do not make "missing" values with quote marks, they aren't "missing" and likely odd to deal with.

It is a good idea to watch the spelling of similarly named variable "number_tranferred1" is not like the others.

Example:

data test; 
input id type1 $ number_transferred1 type2 $ number_transferred2 type3 $ number_transferred3; 
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
4 Yellow . . . . .
5 Yellow 0 . . . . 
6 Yellow 0 Yellow 5 . . 
; 
run; 

The dot is read as missing for character variables as well.

This seems to work with the (corrected) example data above

data want;
   set test;
   array n{*} Number_transferred1 Number_transferred2 Number_transferred3;
   array t(*) type1-type3;

    do i=1 to dim(n);
        if t[i]='Yellow' then Number_transferred_total=sum(Number_transferred_total,n[i]);
    end;
    drop i;
run; 

Key bit: you said the total was based on values of Yellow but did not implement any.

The addition operator +, by design returns missing for the sum when any of the variables have a missing value. However the SAS SUM function will return the sum of any non-missing values provided as arguments to the function.

Note that I used an array with the TYPE to match the corresponding transferred values and only sum when your stated condition is met. Also unless you really like having more opportunities to misspell your variables I suggest using much shorter names of the arrays.

[PaigeMiller]

This is a job for ARRAYs and the SUM function

data test; 
input id type1 $ number_transferred1 type2 $ number_transferred2 type3 $ number_transferred3; 
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
4 Yellow . "" . "" .
5 Yellow 0 "" . "" . 
6 Yellow 0 Yellow 5 "" . 
; 
run; 

data want;
    set test;
    array type $ type1-type3;
    array nt number_transferred1-number_transferred3;
    do i=1 to dim(nt);
        if type(i)='Yellow' then total_number_transferred=sum(total_number_transferred,nt(i));
    end;
    drop i;
run;

Thank you for providing the data as a SAS data step. However, help yourself and help us as well by testing the code beforehand, please don't provide us with code that doesn't work.