Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Re: Summing variable and then counting back down

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 01-29-2016 01:31 PM
(1235 views)

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.

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.