topic Re: retain value until last.id if treshold excedeed in SAS Programming

retain value until last.id if treshold excedeed

goularts — Thu, 02 Sep 2021 02:16:56 GMT

I have the following table that cumulate value along the day by id and when the treshold (-150) is exceeded i need a flag and keep it until last.id

ID	value	cumulative_value	flag
1	35	35	0
1	35	70	0
1	100	170	0
1	-110	60	0
1	35	95	0
2	15	15	0
2	35	50	0
2	-130	-80	0
3	40	40	0
3	-125	-85	0
3	-90	-175	1
3	35	-140	1
3	35	-105	1
3	35	-70	1

I've tried a lot of lag and retains structures but it doesn't work.

Re: retain value until last.id if treshold excedeed

Astounding — Thu, 02 Sep 2021 02:29:35 GMT

A straightforward approach could work as follows:

data want;
   set have;
   by id;
   retain flag;
   if first.id then flag=0;
   if cumulative_value < -150 then flag=1;
run;

This assumes you know how to get the cumulative value (or perhaps it is already in your data.) If you need to compute it along the way, here's a modification that would do that:

data want;
   set have;
   retain flag;
   if first.id then do;
      flag=0;
      cumulative_value=0;
   end;
   cumulative_value + value;
   if cumulative_value < -150 then flag=1;
run;

Re: retain value until last.id if treshold excedeed

goularts — Thu, 02 Sep 2021 03:27:49 GMT

Worked perfectly!! Just made a change and included "by id":

data want;
   set have;
   by id;
   retain flag;
   if first.id then do;
      flag=0;
      cumulative_value=0;
   end;
   cumulative_value + value;
   if cumulative_value < -150 then flag=1;
run;

thanks