topic Re: Get rows when value of a column reach every multiplication of 15 in SAS Programming

Get rows when value of a column reach every multiplication of 15

saf_nadia — Sun, 05 Aug 2018 15:00:35 GMT

Hi, I have a dataset where the last row has a total cummulative entries value. This value(1094) need to be divided by 12. Then i need to get rows of the dataset when value the cummulative column reach the multiplier value. Eg, 1094/12=91.6.. The output is to get rows when cummulative entries reach 91, 182,273,.... Example,

Name Entries Cumm_entries
Myn 70 70
Ann 85 155
Dave 24 179
Amy 39 218
Ram 82 300
Bay 9 309
.
.
.
Tom 7 1094
Since values of cumm entries of 91, 182,273 in row 2nd, 4th and 5th rows, the output that i want is as below:

Name Entries Cumm_entries
Ann 85 155
Amy 39 218
Ram 82 300

Appreciate if any of sifu here can help me.

Re: Get rows when value of a column reach every multiplication of 15

PaigeMiller — Sun, 05 Aug 2018 15:31:33 GMT

UNTESTED CODE

data want;
    set have;
    prev_cumm = lag(cumm_entries);
    if floor(cumm_entries/91) ^= floor(prev_cumm/91) then output;
run;

Re: Get rows when value of a column reach every multiplication of 15

saf_nadia — Sun, 05 Aug 2018 15:38:56 GMT

Hi, thanks for your reply. I have few datasets and each has different sizing hence total cummulative also would be different. But each dataset need to be divided by 12. How can I program to get the total cummulative value and divide by 12 for each dataset and then get the rows that have multiplier in it?

Re: Get rows when value of a column reach every multiplication of 15

PaigeMiller — Sun, 05 Aug 2018 15:44:07 GMT

proc sql noprint;
    select sum(entries)/12 into :sizing from have;
quit;
data want;
    set have;
    prev_cumm = lag(cumm_entries);
    if floor(cumm_entries/&sizing) ^= floor(prev_cumm/&sizing) then output;
run;

Re: Get rows when value of a column reach every multiplication of 15

saf_nadia — Mon, 06 Aug 2018 16:46:10 GMT

I’ve tested but the output is not like I want it to be. The output should be 12 rows only and my cumm_entries values also seem modified

Re: Get rows when value of a column reach every multiplication of 15

PaigeMiller — Mon, 06 Aug 2018 16:54:10 GMT

Show us the output you get. Show us the output you want to get. Oh, yeah, also give us the complete data set you are working with.

Re: Get rows when value of a column reach every multiplication of 15

saf_nadia — Tue, 07 Aug 2018 04:41:22 GMT

Hi,
Have tried the code. It does work for 2nd-11th row of output but nit the
first row. It takes first row of the data as the first row of output. The
output should exclude the first row but include the last row since
sizing/12. But the last row of dataset is not the output here.