topic Re: Dividing the value in SAS Programming

Dividing the value

niloya — Thu, 14 Mar 2019 20:29:40 GMT

I just would like to divide x column to y column. and the output should be spread over the earlier than x value.

The total spreading value in the desired output should equal x value. For example, x =16 it was divided to 4. So, in the output, there should be 4, 4,4,4

Input

x	y
0	4
0	4
0	4
0	4
0	4
16	4
0	4
0	4
0	4
0	4
0	4
0	4
16	4
0	4
0	4
0	4
0	4
0	4
0	4
16	4
0	4

x	y	Desired Output
0	4
0	4
0	4	4
0	4	4
0	4	4
16	4	4
0	4
0	4
0	4
0	4	4
0	4	4
0	4	4
16	4	4
0	4
0	4
0	4
0	4	4
0	4	4
0	4	4
16	4	4
0	4

Re: Dividing the value

mkeintz — Thu, 14 Mar 2019 20:50:08 GMT

This is a task where one approach is to "look ahead". My question is, will the precipitating record always have X=16 and Y=4? If so, then you can always look ahead by a constant number of records.

Re: Dividing the value

niloya — Thu, 14 Mar 2019 20:55:03 GMT

@mkeintz numbers can change and would be different numbers. This is just a data to show I would like to have. 16 could be 12 , or 20 and 4 could be 2 or 3.

Re: Dividing the value

Shmuel — Thu, 14 Mar 2019 23:20:52 GMT

What would be the desired output if the division result is not an integer ?

I would:

1- add to the dataset a sequence number,

2- sort it by descending sequnce,

3- make the division as long as X is not zero and retain X as saved_X and the dividend

4- for any X=0 assign the dividend, subtract it from saved_X until saved_X le 0

data temp1;
 set have;
      seq = _N_;
run;
proc sort data=temp1; by descending seq; run;

data temp2(keep=seq x y div);
  set temp1;
        retain saved_x div;
        if x ne 0 then do;
           saved_x = x;
           div = int(x / y); 
           output;
        end;
        else do;
           saved_x = saved_x - div;
           if saved_x > div then output;
           else do;
               div=saved_x;
               output;
          end;
      end;
run;

proc sort data=temp2 
           out=want(keep=x y div);
     by seq;
run;

Re: Dividing the value

Yavuz — Thu, 14 Mar 2019 23:17:10 GMT

What do you expect to see if the results cross
For example;
X Y
0 4
12 4
16 4
is this combination possible ?

Re: Dividing the value

Ksharp — Fri, 15 Mar 2019 14:34:10 GMT

data have;
infile cards expandtabs;
input x	y ;
cards;
0	4
0	4
0	4
0	4
0	4
16	4
0	4
0	4
0	4
0	4
0	4
0	4
16	4
0	4
0	4
0	4
0	4
0	4
0	4
16	4
0	4
;
data have;
 set have;
 n+1;
 if x>0 and y>0 then z=x/y;
run;
data key;
 set have(where=(z is not missing))	;
 do k=n-3 to n;
  output;
 end;
 keep k z;
run;
data want;
 if _n_=1 then do;
  if 0 then set key;
  declare hash h(dataset:'key');
  h.definekey('k');
  h.definedata('z');
  h.definedone();
 end;
 set have;
 rc=h.find(key:n);
 drop rc k n;
run;