Calcite | Level 5

## Summing variable and then counting back down

Hii,

I have some insect data where I have counts insects (Sum Adults), but I don't know if they're males or females until they die and are dissected (DeadFem and DeadMale).  When they are all dead, then I summed up how many there total (TotFem).  I want to know how many females there are on a given date to do a fecundity calculations (#females/#eggs laid).

18 Apr               17                     0            0              0

22 Apr               14                     1             2             1

25 Apr                12                    2             0             3

.

.

and so on. The last entry in TotFem tells me how many total females I had, so then I want a variable that starts at that value and subtracts the value of DeadFem for each date.

5 REPLIES 5
Super User

## Re: Summing variable and then counting back down

This may give you something to work with. I strongly recommend having an actual SAS date value including a year.

data have;
datalines;
18 Apr               17                     0            0              0
22 Apr               14                     1             2             1
25 Apr                12                    2             0             3
;
run;
/* it would be preferable to have an actual complete SAS date value to sort by*/
proc sort data=have; by descending day;run;

data want;
set have;
retain alive 0;
output;
run;

proc sort data=want; by day;run;
Barite | Level 11

## Re: Summing variable and then counting back down

It's best to give an example of your desired output.  I think all you need is a running total of the dead male, then you can do any calculation you want.  Let me know if this helps:

data have;
cards;
18Apr               17                    0            0             0
22Apr               14                    1            2             1
25Apr               12                    2            0             3
;run;

data want;
set have;
run;

Opal | Level 21

## Re: Summing variable and then counting back down

I am not sure I understand the problem. Here is my interpretation:

At a given date (t), all you know is

• TotFem(t) <= Females(t) <= SumAdults(t) - TotMales(t)
• So, if EggsLaid(t) is the number of eggs laid between t-1 and t, Fecundity(t) (EggsLaid(t)/ Females(t-1)) is bounded between EggsLaid(t) / (SumAdults(t-1) - TotMales(t-1)) and EggsLaid(t) / TotFem(t).

Is that right?

PG
Calcite | Level 5

## Re: Summing variable and then counting back down

I am not sure that solves the problem.  Maybe my real question is how to I inialize a new variable with something other than 0. I want it to be the final value of the totfem variable.

I had created all these counting variables to keep track of how many male and female insects died each day as well as the total number of insects. What I need is the last value of totfem to be the initial value of a new variable (livefem).  From there I think I can write a do loop to subtract the daily value of sumfem.

data sasuser.sumhome;
set sasuser.sorthome;
retain  sumadult sumfem summale sumegg cnt days sumlost;
by Date2;

days = Date2-Date1;

if first.Date2 then
do;
sumlost =0;
sumfem = 0;
summale = 0;
sumegg = 0;

cnt = 0;
end;

sumlost= sumlost+lost;
sumegg= sumegg+eggs;

cnt= cnt+1;

if last.Date2 then output;

run;
data sasuser.hometot;
set sasuser.sumhome;
retain sumadult sumfem summale sumegg cnt days totfem ;
by Date2;
drop Cage Lost Alive Eggs;

totfem+sumfem;
run;
Obsidian | Level 7

## Re: Summing variable and then counting back down

does this get you close?

data howmany ;
infile datalines dsd delimiter=',';
format day date10. ;
datalines;
18 Apr 16,17,0,0,0
22 Apr 16,14,1,2,1
25 Apr 16,12,2,0,3
;
run;

title1 'raw input';
proc print data = howmany ;
run;

title1 'rolling total of dead female bugs' ;
proc sql ;
create table rolling as
select d.day,SUM(r.totfem) as rollingtotfem
from
howmany d
join
howmany r
on d.day >= r.day
group by d.day
;
quit;
run;

title1 'apply rolling total back to raw data' ;
proc sql ;