topic to derive a value based on the previous value in SAS Studio

to derive a value based on the previous value

AKHILA — Mon, 13 May 2019 09:33:50 GMT

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

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.

Re: to derive a value based on the previous value

Jagadishkatam — Mon, 13 May 2019 10:12:13 GMT

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;

Re: to derive a value based on the previous value

Ksharp — Mon, 13 May 2019 13:28:24 GMT

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;

Re: to derive a value based on the previous value

Astounding — Mon, 13 May 2019 14:03:29 GMT

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

FreelanceReinh — Mon, 13 May 2019 18:26:52 GMT

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.