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.
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.