Obsidian | Level 7

## to derive a value based on the previous value

Hi all,

In the  dataset below;

visit    var1          count

1      1               a1

1      2               a2

1      3               a3

1      000            no

2     1                b1

2      2               b2

2      000           no

2      000           no

if the count value is 'no' then var1 should be mapped to the previous var1 value.if the same visit value then the var1 should follow an order like,

visit    var1          count

1      1               a1

1      2               a2

1      3               a3

1      3.1           no

2     1                b1

2      2               b2

2      2.1          no

2      2.2          no....etc.

4 REPLIES 4
Amethyst | Level 16

## Re: to derive a value based on the previous value

you may try

``````data have;
input visit    var1          count\$;
cards;
1 1 a1
1 2 a2
1 3 a3
1 000 no
2 1 b1
2 2 b2
2 000 no
2 000 no
;

data want;
set have;
by visit var1 notsorted;
retain visitn ;
if var1<=0 then var1=.;
if first.visit then visitn=.;
if var1 ne . then visitn=var1;
run;

data want2;
set want;
by visit visitn notsorted;
if first.visitn then visn=visitn;
else visn=visitn+0.1;
run;``````
Thanks,
Jag
Super User

## Re: to derive a value based on the previous value

``````data have;
input visit    var1  \$        count \$;
cards;
1 1 a1
1 2 a2
1 3 a3
1 000 no
2 1 b1
2 2 b2
2 000 no
2 000 no
;
data temp;
set have;
by visit;
if first.visit or count ne 'no' then group+1;
run;
data want;
set temp;
by group;
length temp \$ 80;
retain temp;
if first.group then do;temp=var1;n=-1;end;
n+1;
if count='no' then var1=cats(temp,'.',n);
run;``````
PROC Star

## Re: to derive a value based on the previous value

It's simpler to start over and create a new variable:

``````data want;
set have;
by id;
if first.id then var1_new = 0;
if count='no' then var1_new + 0.1;
else var1_new + 1;
run;``````

Notice that you can have a problem if there are 10 or more consecutive "no" values for COUNT.

## Re: to derive a value based on the previous value

Also, note that non-integer IDs in a numeric variable are prone to numeric representation issues, i.e. small rounding errors that make numeric values different from what they look like in common display formats. Example: Duplicate the last record of your sample data and then try to select the corresponding record in the WANT dataset with WHERE var1_new=2.3; (using Astounding's variable name).

Depending on how var1_new will be used, I would either define it as a character variable (see Ksharp's suggestion, note potential sort order issues, though, e.g. '10'<'2') or apply the ROUND function (e.g. var1_new=round(var1_new, 1e-6);) and still be careful when using these values.

Discussion stats
• 4 replies
• 588 views
• 0 likes
• 5 in conversation