Calcite | Level 5

looping by rows with condition

`data have;input  var1 6.2  var2 6.2  var3 6.2;datalines;1400.00 165.55  839.570.00	0.00	106.421200.00	142.98	725.090.00	0.00	91.91270.00	0.00	168.850.00	0.00	67.540.00	0.00	33.61270.00	0.00	0.000.00	0.00	168.850.00	0.00	101.15155.00	1.70	150.000.00	0.00	3.30;run;Hello I want to verify if var1 < (var2+var3) by group (while I have 0.00) (1)exemple:  1400.00 with 165.55 + 839.57 +106.42          1200.00 with 142.98 + 725.09 + 91.91          270.00 with 168.85 + 67.54 +33.61want = new column 1 if i Have (1)                  0 else`

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Employee

Re: looping by rows with condition

This works, but there is not a single case in the "have" table that meets condition (1).

``````data have;
input  var1   var2   var3 ;
datalines;
1400.00 165.55  839.57
0.00	0.00	106.42
1200.00	142.98	725.09
0.00	0.00	91.91
270.00	0.00	168.85
0.00	0.00	67.54
0.00	0.00	33.61
270.00	0.00	0.00
0.00	0.00	168.85
0.00	0.00	101.15
155.00	1.70	150.00
0.00	0.00	3.30
;
run;

data have1;
set have;
retain group 0;
if var1 ne 0 then group=group+1;
run;

data want;
set have1;
by group;
retain sum_var1 0 sum_var2_var3 0;

if first.group and last.group=0 then do;
sum_var1=var1;
sum_var2_var3=sum(var2,var3);
end;

else if first.group=0 and last.group=0 then do;
sum_var1=sum(sum_var1,var1);
sum_var2_var3=sum(sum_var2_var3,sum(var2,var3));
end;

else do;
sum_var1=sum(sum_var1,var1);
sum_var2_var3=sum(sum_var2_var3,sum(var2,var3));
if sum_var1 < sum_var2_var3 then new=1;
else new=0;
end;
drop sum_var1 sum_var2_var3;
run;

``````

2 REPLIES 2
Super User

Re: looping by rows with condition

You really need to add a variable to your data that indicates which "group" a set of records belongs to or explicitly define a rule that will let us know when a group begins. You don't provide that currently.

Otherwise there really isn't a consistent way to see what a "group" may actually be.

You also need to show exactly how you expect your result to appear.

SAS Employee

Re: looping by rows with condition

This works, but there is not a single case in the "have" table that meets condition (1).

``````data have;
input  var1   var2   var3 ;
datalines;
1400.00 165.55  839.57
0.00	0.00	106.42
1200.00	142.98	725.09
0.00	0.00	91.91
270.00	0.00	168.85
0.00	0.00	67.54
0.00	0.00	33.61
270.00	0.00	0.00
0.00	0.00	168.85
0.00	0.00	101.15
155.00	1.70	150.00
0.00	0.00	3.30
;
run;

data have1;
set have;
retain group 0;
if var1 ne 0 then group=group+1;
run;

data want;
set have1;
by group;
retain sum_var1 0 sum_var2_var3 0;

if first.group and last.group=0 then do;
sum_var1=var1;
sum_var2_var3=sum(var2,var3);
end;

else if first.group=0 and last.group=0 then do;
sum_var1=sum(sum_var1,var1);
sum_var2_var3=sum(sum_var2_var3,sum(var2,var3));
end;

else do;
sum_var1=sum(sum_var1,var1);
sum_var2_var3=sum(sum_var2_var3,sum(var2,var3));
if sum_var1 < sum_var2_var3 then new=1;
else new=0;
end;
drop sum_var1 sum_var2_var3;
run;

``````

Discussion stats
• 2 replies
• 403 views
• 0 likes
• 3 in conversation