SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Bin numeric variable and count them

Reply
Contributor
Posts: 31

Bin numeric variable and count them

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,

INCOMETARGET
00
01
10
220
350
1000
1500
1880
2070
2480
2800
2930
3300
3400
3800
4000
4450
5200
5800
6500
7000
8650
9000
10000
10620
12000
12150
14000
15600
16001
17000

 

The bin: 

BIN 
00-170
170170-340
340340-510
510510-680
680680-850
850850-1020
10201020-1190
11901190-1360
13601360-1530
15301530-1700
1700 

 

The table I want to have finally:

 

INCOME COUNTTARGET_1TARGET_0 
0-170716
170-340808
340-510303
510-680303
680-850101
850-1020303
1020-1190101
1190-1360202
1360-1530101
1530-1700312

 

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.

 

 

PROC Star
Posts: 252

Re: Bin numeric variable and count them

[ Edited ]

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

Super User
Super User
Posts: 7,392

Re: Bin numeric variable and count them

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.

Ask a Question
Discussion stats
  • 2 replies
  • 118 views
  • 0 likes
  • 3 in conversation