Help using Base SAS procedures

Summing variable and then counting back down

New Contributor
Posts: 3

Summing variable and then counting back down



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). 


Date              SumAdults   DeadFem DeadMale TotFem

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. 



Super User
Posts: 13,538

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;
input day month $        SumAdults   DeadFem DeadMale TotFem;
18 Apr               17                     0            0              0
22 Apr               14                     1             2             1   
25 Apr                12                    2             0             3
/* 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;
   alive = alive + deadfem;

proc sort data=want; by day;run;
Valued Guide
Posts: 863

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;
input Date$              SumAdults   DeadFem DeadMale TotFem;
18Apr               17                    0            0             0
22Apr               14                    1            2             1
25Apr               12                    2            0             3

data want;
set have;

Esteemed Advisor
Posts: 5,526

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?

New Contributor
Posts: 3

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
sumlost =0;
sumadult = 0;
sumfem = 0;
summale = 0;
sumegg = 0;

cnt = 0;

sumlost= sumlost+lost;
sumadult = sumadult+alive;
sumfem = sumfem+deadfem;
summale = summale+deadmale;
sumegg= sumegg+eggs;

cnt= cnt+1;

if last.Date2 then output;

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

Occasional Contributor
Posts: 7

Re: Summing variable and then counting back down

does this get you close?


data howmany ;
   infile datalines dsd delimiter=','; 
input day DATE10. sumAdults deadFem deadMale totFem ;
format day date10. ;
18 Apr 16,17,0,0,0
22 Apr 16,14,1,2,1
25 Apr 16,12,2,0,3

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

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

title1 'apply rolling total back to raw data' ;
proc sql ;
select,d.sumAdults ,d.deadFem ,d.deadMale ,d.totFem , r.rollingtotfem
from howmany d
join rolling r
on =;
quit ;
Ask a Question
Discussion stats
  • 5 replies
  • 5 in conversation