topic Re: Summing variable and then counting back down in SAS Procedures

Summing variable and then counting back down

kebowers — Fri, 29 Jan 2016 18:31:45 GMT

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

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.

Re: Summing variable and then counting back down

ballardw — Fri, 29 Jan 2016 19:05:30 GMT

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;
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;
   alive = alive + deadfem;
run;

proc sort data=want; by day;run;

Re: Summing variable and then counting back down

Steelers_In_DC — Fri, 29 Jan 2016 19:34:51 GMT

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;
cards;
18Apr               17                    0            0             0
22Apr               14                    1            2             1
25Apr               12                    2            0             3
;run;

data want;
set have;
TotMal+deadmale;
run;

Re: Summing variable and then counting back down

PGStats — Fri, 29 Jan 2016 22:01:43 GMT

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?

Re: Summing variable and then counting back down

kebowers — Tue, 16 Feb 2016 15:21:07 GMT

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


cnt = 0;
end;

sumlost= sumlost+lost;
sumadult = sumadult+alive;
sumfem = sumfem+deadfem;
summale = summale+deadmale;
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;

Re: Summing variable and then counting back down

johnsville — Wed, 17 Feb 2016 15:13:17 GMT

does this get you close?

data howmany ;
   infile datalines dsd delimiter=','; 
input day DATE10. sumAdults deadFem deadMale totFem ;
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 ;
select d.day,d.sumAdults ,d.deadFem ,d.deadMale ,d.totFem , r.rollingtotfem
from howmany d
join rolling r
on d.day = r.day;
quit ;
run;