topic Bin numeric variable and count them in SAS Data Management

Bin numeric variable and count them

JinboZhao — Thu, 20 Jul 2017 12:54:37 GMT

Hi SAS,

I have a data set with a target variable which only contains 1 and 0, and other about 60 independent numeric variables. For each variable, I would like to bin them into 10 same width groupds, and then count the frequency of target numbers in each group.

For example, variable INCOME,

INCOME	TARGET
0	0
0	1
1	0
22	0
35	0
100	0
150	0
188	0
207	0
248	0
280	0
293	0
330	0
340	0
380	0
400	0
445	0
520	0
580	0
650	0
700	0
865	0
900	0
1000	0
1062	0
1200	0
1215	0
1400	0
1560	0
1600	1
1700	0

The bin:

BIN
0	0-170
170	170-340
340	340-510
510	510-680
680	680-850
850	850-1020
1020	1020-1190
1190	1190-1360
1360	1360-1530
1530	1530-1700
1700

The table I want to have finally:

INCOME	COUNT	TARGET_1	TARGET_0
0-170	7	1	6
170-340	8	0	8
340-510	3	0	3
510-680	3	0	3
680-850	1	0	1
850-1020	3	0	3
1020-1190	1	0	1
1190-1360	2	0	2
1360-1530	1	0	1
1530-1700	3	1	2

I can do it step by step, like bin them in EXCEL, then replace the INCOME with bins value, then count the numbers . But I do not know how to do them in SAS step by step. Also, I have more than 60 numeric variables, is there any easier way to deal them in SAS?

Many appreciation.

Re: Bin numeric variable and count them

kiranv_ — Thu, 20 Jul 2017 13:17:33 GMT

you can do this by using proc format.

A similar but not exactly same logic is given in the article below.

http://blogs.sas.com/content/iml/2016/08/08/sas-formats-bin-numerical-variables.html

Re: Bin numeric variable and count them

RW9 — Thu, 20 Jul 2017 13:17:17 GMT

Post example test data in the form of a datastep! From the info you provided I would say creating a format for the "bins" and applying this to your data, then summing/grouping - e.g. proc freq by the formatted value, would yield the result you want, or you could do it in SQL.