Contributor
Posts: 29

# Retain and by group processing

My data looks like this:

Obs change Md_mg M_mg
1 0 0 8.7912
2 1 0.00232 .
3 2 0.00304 .
4 3 0.0032 .
5 4 0.00304 .
6 5 0.00784 .
7 6 0.0248 .
8 7 0.0328 .
9 8 0.044 .
10 9 0.0224 .
11 10 0.0312 .
12 11 0.028 .
13 12 0.0328 .
14 13 0.0256 .
15 14 0.028 .
16 0 0 8.8992
17 1 0.00248 .
18 2 0.00288 .
19 3 0.00304 .
20 4 0.00312 .
21 5 0.00776 .
22 6 0.0216 .
23 7 0.0328 .
24 8 0.0448 .
25 9 0.0224 .
26 10 0.036 .
27 11 0.0312 .
28 12 0.0336 .
29 13 0.028 .
30 14 0.0264 .

I *simply* want to fill in the missing values for M_mg in a check book balancing type of process, subtracting Md_mg each time; in other words for observation 2 M_mg should equal 8.78888, for observation 3 it should equal 8.78584, and so on. Resetting at the change variable 0 (0 is first.).

I've tried various combinations of the retain statement, first., etc. and appear to be stuck. Help appreciated! Thank you. NWV
Posts: 3,852

## Re: Retain and by group processing

[pre]
data change;
input Obs change Md_mg M_mg0;
if change eq 0 then M_mg = m_mg0;
else m_mg = m_mg - md_mg;
retain m_mg;
cards;
1 0 0 8.7912
2 1 0.00232 .
3 2 0.00304 .
4 3 0.0032 .
5 4 0.00304 .
6 5 0.00784 .
7 6 0.0248 .
8 7 0.0328 .
9 8 0.044 .
10 9 0.0224 .
11 10 0.0312 .
12 11 0.028 .
13 12 0.0328 .
14 13 0.0256 .
15 14 0.028 .
16 0 0 8.8992
17 1 0.00248 .
18 2 0.00288 .
19 3 0.00304 .
20 4 0.00312 .
21 5 0.00776 .
22 6 0.0216 .
23 7 0.0328 .
24 8 0.0448 .
25 9 0.0224 .
26 10 0.036 .
27 11 0.0312 .
28 12 0.0336 .
29 13 0.028 .
30 14 0.0264 .
;;;;
run;
proc print;
run;
[/pre]
Contributor
Posts: 29

## Re: Retain and by group processing

Thank you.

I use your suggestion and ended up doing something similar, although I did use the first. instead of if change=0.

I have another variable sampletype that uniquely codes the groups, so with first.sampletype=m_mg0 (now called T0_mass), I then retained the m_mg and subtracted md_mg--the code looked like this and produced the needed result:

data d4;
set d3;
by sample change;
retain M_mg;
if first.sample then M_mg=T0_mass;
M_mg=M_mg-Md_mg;
proc print data=d4;run;

Thank you again for the clue the provided my solution.
Discussion stats
• 2 replies
• 127 views
• 0 likes
• 2 in conversation