topic Rebinning data by .4 instead of .2 in SAS Programming

Rebinning data by .4 instead of .2

marksanter — Sat, 13 Mar 2021 22:45:20 GMT

Hello,

I currently have a have a dataset where values are binned by .2. I would like to bin the values by .4. To do that, I need to add up values in each pair of bins (add up for 0.8 and 1 and assign it to a summed bin at 0.9 for example and so forth). I have attached an example data set. Would someone be able to assist me in this?

Thank you,

Mark

Re: Rebinning data by .4 instead of .2

ballardw — Sun, 14 Mar 2021 06:24:02 GMT

How were the values "binned by .2" in the first place.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Or look at the many examples of data step code with Datalines or Cards statements on the forum.

Re: Rebinning data by .4 instead of .2

PGStats — Sun, 14 Mar 2021 17:19:02 GMT

Binning can be tricky.... Here I assume that you want the new bin value to be the center of the new bin even when you have data only for half of the new bin range (as for the last bin of id=2):

data have;
input ID Bin Value;
datalines;
1 0.8 312
1 1   123
1 1.2 678
1 1.4 534
1 1.6 456
1 1.8 32
2 0.8 567
2 1   678
2 1.2 678
2 1.4 23
2 1.6 468
2 1.8 4678
2 2   4678
3 0.8 8
3 1   8
3 1.2 345
3 1.4 678
3 1.6 345
3 1.8 567
4 0.8 789
4 1   80
4 1.2 456
4 1.4 46
4 1.6 678
4 1.8 789
5 0.8 24
5 1   46
5 1.2 567
5 1.4 678
5 1.6 890
5 1.8 456
6 0.8 567
6 1   567
6 1.2 68
6 1.4 345
6 1.6 57
6 1.8 678
7 0.8 789
7 1   890
7 1.2 4456
7 1.4 789
7 1.6 234
7 1.8 567
8 0.8 789
8 1   890
8 1.2 354
8 1.4 456
8 1.6 564
8 1.8 678
9 0.8 709
9 1   123
9 1.2 34
9 1.4 35
9 1.6 456
9 1.8 576
;

%let oldBinSize=0.2;
%let newBinSize=0.4;

proc sql;
create table want as
select
    id,
    int(bin/&newBinSize.)*&newBinSize. + &oldBinSize./2 as newBin,
    sum(value) as newValue
from
    have
group by id, newBin;
quit;

proc print data=want noobs; run;

Re: Rebinning data by .4 instead of .2

Sajid01 — Sun, 14 Mar 2021 17:19:04 GMT

You can do this by creating formats.
This should help https://blogs.sas.com/content/iml/2016/08/08/sas-formats-bin-numerical-variables.html