Hi
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 |
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.
@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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.