Dear Friends ,
I have 20 years income data of household level. for that i calculate Percentiles now i need to group like 1-2 percentiles are group 1 and 3-4 are group 2 like up to ten i need to group.
in SAS i need to write if and then codes for each year. so i dont have idea to use arrays. please help me in this.
I attached data for your information.
Table 1: Percentiles
2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 |
3840 | 9795 | 17461 | 13203 | 16994 | 23436 | 25350 | 22863 | 27005 | 31948 | 23443 |
8679 | 14680 | 23152 | 20181 | 28419 | 34726 | 37602 | 32400 | 49392 | 58940 | 40204 |
16138 | 22036 | 28532 | 27956 | 34871 | 41041 | 55859 | 39890 | 65261 | 93045 | 54077 |
22020 | 27066 | 31435 | 35208 | 45183 | 48419 | 66204 | 51000 | 85441 | 112134 | 79532 |
27144 | 33238 | 40500 | 44160 | 57090 | 59760 | 90264 | 69334 | 97242 | 140537 | 96975 |
31633 | 43346 | 49200 | 54060 | 70867 | 72069 | 118168 | 87502 | 115363 | 164003 | 130210 |
40473 | 54767 | 58063 | 66744 | 80877 | 89957 | 135285 | 101120 | 129027 | 190376 | 156643 |
51504 | 65944 | 72027 | 81012 | 97269 | 109091 | 172723 | 135893 | 150455 | 236750 | 183306 |
65590 | 85163 | 102789 | 105525 | 138842 | 155322 | 214957 | 164344 | 200579 | 332079 | 231901 |
140170 | 218391 | 230555 | 266050 | 274477 | 255686 | 653227 | 379596 | 426260 | 700925 | 739109 |
Table 2 : Data of Household income
Inc2001 | Inc2002 | Inc2003 | Inc2004 | Inc2005 | Inc2006 | Inc2007 | Inc2008 | Inc2009 | Inc2010 | Inc2011 |
5400 | 17800 | 19890 | 23450 | 22500 | 34600 | |||||
22300 | 22500 | 31000 | 33225 | 42710 | 45606 | 55940 | 57600 | |||
52000 | 68000 | 58000 | 28500 | 35990 | 48960 | 77800 | 32400 | |||
20000 | 138000 | 110000 | 108000 | 153950 | 154720 | 217800 | 321000 | |||
5000 | 6150 | 41000 | 5250 | 41543 | 10796 | 10300 | 19560 | |||
35600 | 21100 | 54030 | 56890 | 72050 | 85050 | |||||
10000 | 12900 | 16278 | 14330 | 19781 | 35750 | 36660 | 37686 | |||
11000 | 9750 | 20000 | 13900 | 34340 | 25090 | 31500 | 21900 | |||
19200 | 10500 | 25000 | 13368 | 12683 | 27212 | 25000 | 2400 | |||
3000 | 8110 | 17500 | 23088 | 11999 | 131860 | 34964 | 27870 | |||
59600 | 30000 | 34733 | 29000 | 34540 | 71454 | 55850 | 96000 | |||
18400 | 29025 | 20000 | 16800 | 44675 | 28241 | 121434 | 36222 | |||
30000 | 21000 | 40000 | 15000 | 31875 | 134130 | 66100 | 99200 | |||
113743 | 136377 | 135627 | 185955 | 226647 | 215625 | 653227 | 307845 | |||
52645 | 81526 | 72977 | 125203 | 80726 | 178238 | 340600 | 310731 | |||
-12089 | 24526 | 19988 | 41058 | 61557 | 41878 | 90810 | 33150 | |||
-34462 | 218391 | 82326 | 218305 | 274477 | 125216 | 176254 | 136524 | |||
-16741 | 120024 | 125711 | 56525 | 70695 | 42883 | 201606 | 67920 | |||
131740 | 126789 | 123574 | 152760 | 98306 | 40268 | 35900 | 52540 | |||
37742 | 68970 | 47403 | ||||||||
108878 | 99558 | 74990 | ||||||||
65280 | 72989 | 29320 | 80828 | 65130 | 70405 | 139360 | 54768 | |||
38064 | 62976 | 71725 | 53039 | 120706 | 75985 | 328916 | 379596 | |||
53248 | 66300 | 63132 | 78740 | 92311 | 79050 | 120076 | 180491 | |||
35643 | 33368 | 48630 | 35979 | 61057 | 48284 | 211973 | 70253 | |||
2952 | 29822 | 21045 | 25632 | 94000 | ||||||
27418 | 14200 | 61437 | 26685 | 9726 | 8711 | 10770 | 26817 | |||
49703 | 26561 | 25541 | 35855 | 74815 | 67361 | 141430 | 138205 | |||
29744 | 56279 | 84483 | 102233 | 81505 | 63433 | 159983 | 268896 | |||
20054 | 47200 | 28545 | 49356 | 39913 | 163095 | 135580 | 118711 | |||
31030 | 43256 | 46516 | 68305 | 127033 | 175012 | 298783 | 147805 | |||
81850 | 88252 | 55149 | 70100 | 17853 | 41155 | 87110 | 57351 | |||
22747 | 26500 | |||||||||
42673 | 43211 | 49303 | 55592 | 74806 | 100025 | 67312 | 164344 | |||
26018 | 23453 | 33415 | 45730 | 23176 | 49084 | 64722 | 90872 | |||
45285 | 72956 | 31520 | 95936 | 95713 | 167885 | 213580 | 125495 | |||
27232 | 57378 | 93079 | 44845 | 45227 | 33813 | 90758 | 27727 | |||
20113 | 35318 | 49243 | 112157 | 29118 | 30006 | 45718 | 27459 | |||
16100 | 13967 | 19208 | 19106 | 38845 | 53520 | 34100 | 51000 | |||
11694 | 9043 | 27750 | 38944 | 65969 | 56642 | 58870 | 144347 | |||
22000 | 14800 | 22500 | 20450 | 35523 | 26574 | 32820 | 43400 | |||
30862 | 20247 | 35050 | 25483 | 18960 | 53970 | 66220 | 62880 | |||
51299 | 39202 | 29212 | 74370 | 65478 | 91657 | 143817 | 159360 | |||
31892 | 44593 | 28873 | 32785 | 77549 | 65686 | 211455 | 69334 | |||
14900 | 18100 | 60000 | 62400 | 43990 | 45900 | 27551 | ||||
12707 | 24450 | 30000 | 35374 | 33365 | 38015 | 57590 | 37460 | |||
23024 | 12918 | 23510 | 34189 | 39994 | 40439 | 38205 | 42314 | |||
27144 | 22437 | 31307 | 29983 | 39147 | 45170 | 52447 | 71418 | |||
20350 | 40370 | 53187 | 43475 | 63388 | 58690 | 67200 | 41853 | |||
4000 | 3000 | 5000 | 4250 | 7388 | 9330 | 10650 | 13020 | |||
36671 | 36184 | 47146 | 102187 | 120462 | 135893 | |||||
24250 | 16500 | 47989 | 93250 | 24900 | 80900 | |||||
32250 | 23550 | 32159 | 43005 | 14676 | 39114 | |||||
106750 | 66075 | 71840 | 103599 | 218199 | 114786 | |||||
52000 | 47700 | 57830 | 74915 | 93342 | 94775 | |||||
52545 | 72173 | 108468 | 89115 | |||||||
38929 | 54890 | 39890 | ||||||||
54275 | 63550 | 48250 | ||||||||
14135 | 48881 | 49172 | 65337 | 82870 | 124896 | 113052 | 162220 | 112924 | 182763 | 157624 |
2383 | 28739 | 58239 | 82130 | 80887 | 80293 | 56801 | 133721 | 159206 | 155261 | 80824 |
21848 | 33495 | 27300 | 21205 | 48460 | 100280 | 156400 | 107642 | 146143 | 214941 | 137191 |
46384 | 47400 | 65385 | 44328 | 86262 | 153862 | 178162 | ||||
43883 | 59782 | 12910 | 17040 | 21928 | 17750 | |||||
66834 | 65924 | 58650 | 45125 | 56350 | 40965 | 89770 | 56156 | 59095 | 4741 | 38956 |
39101 | 44860 | 43245 | 54241 | 21559 | ||||||
33580 | 35209 | 48120 | 82634 | 142977 | 70990 | |||||
50815 | 36970 | 55200 | 149503 | 112446 | 126710 | 89576 | ||||
79439 | 155450 | 156981 | ||||||||
12146 | 91104 | 38095 | 297183 | 150729 | 116632 | |||||
12000 | 19750 | 17500 | 16660 | 19395 | 28447 | 40000 | 45100 | 104460 | 57470 | 53220 |
30400 | 24950 | |||||||||
34798 | 47408 | 79525 | 84999 | 91350 | 84784 | 97748 | ||||
35750 | 26964 | 89380 | 32170 | 27780 | ||||||
3600 | 3750 | 28500 | 5175 | 8728 | 8880 | 10000 | 15330 | 17170 | 12750 | 6850 |
36751 | 84820 | 61250 | 102864 | 159861 | 46477 | 81380 | 36385 | 75703 | 90742 | 96202 |
87407 | 132633 | 95985 | 67500 | |||||||
94450 | 60800 | |||||||||
21140 | 33108 | 28577 | 15315 | 3020 | 23098 | 66139 | 22863 | 16313 | 96530 | 52376 |
48291 | 51631 | 81550 | 74683 | 87455 | 78242 | 119018 | 120424 | 119022 | 333448 | 212475 |
54131 | 102246 | 56700 | 80340 | 108199 | 110985 | 202025 | 160085 | 165351 | 331927 | 227106 |
5700 | 3600 | 10500 | 5190 | 3390 | ||||||
70525 | 66023 | 54890 | 81748 | 107482 | 99693 | 174582 | 101120 | 182760 | 546229 | 275093 |
37347 | 44603 | 39171 | 51076 | 80790 | 60215 | 115370 | 46166 | 84628 | 147171 | 88868 |
3800 | ||||||||||
-13265 | 29557 | 31892 | 25111 | 33404 | 62228 | 77828 | 99657 | 102266 | 119304 | 173866 |
28755 | 19515 | 24850 | 33866 | 64382 | 109411 | 121350 | 109292 | 309560 | 111499 | 91021 |
6000 | 5050 | 11711 | 11715 | 21038 | 31135 | 19583 | 28390 | 99851 | 152544 | 61786 |
32941 | 234181 | 281548 | 32250 | 34962 | 36426 | 50580 | ||||
42900 | 25900 | 28750 | 21588 | 76383 | 40000 | 30175 | 38303 | 59700 | 99140 | 158440 |
22100 | 17100 | 24250 | 18643 | 27153 | 27960 | 33500 | 31190 | 50397 | 59308 | 43285 |
33310 | 43480 | 50776 | 96158 | 51270 | 60083 | 124363 | 93150 | 125433 | 135015 | 35260 |
22695 | 36004 | 30436 | 30223 | |||||||
154713 | 98355 | 359660 | 132567 | 198383 | 350541 | 138916 | ||||
29063 | 23296 | 23315 | 20610 | 49422 | 51167 | 138613 | 125661 | 135327 | 257656 | 162806 |
6000 | 12000 | 8000 | 9625 | 20130 | 19010 | 13000 | 21940 | 40170 | 85960 | 22540 |
191060 | 112676 | 200538 | 166973 | 173419 | 201331 | 243089 | ||||
18401 | 15103 | 11350 | 16485 | 23830 | 19720 | 19500 | ||||
114498 | 115514 | 217689 | 128852 | 220341 | 281465 | 142705 | ||||
70167 | 107373 | 213786 | 229640 | 149337 | 94032 | 127863 | ||||
46227 | 12810 | 25500 | 18450 | |||||||
23380 | 43918 | 121950 | 158605 | 109260 | 86360 | |||||
24286 | 27402 | 17114 | 28501 | 28340 | 35166 | 47832 | 41390 | 39350 | 38736 | 30550 |
8300 | 13300 | 11750 | 9300 | 9621 | 10123 | 11888 | 13056 | 23760 | 29950 | 23830 |
45005 | 30829 | 36461 | 32783 | 60036 | 62578 | 66603 | ||||
44164 | 73441 | 107048 | 74288 | 71934 | 112557 | 72510 | ||||
130500 | 182300 | 211000 | 242800 | |||||||
56251 | 51830 | 82078 | 48522 | 34638 | 59760 | 77118 | 69032 | 83611 | 250184 | 129127 |
140170 | 134528 | 167786 | 168610 | 120036 | 158398 | 293921 | 214051 | 226720 | 394032 | 219259 |
14066 | 34465 | 5000 | 2900 | 17750 | 22100 | 16450 | ||||
16196 | 60059 | 73490 | 105347 | 84823 | 76643 | 142614 | 149791 | 95175 | 158047 | 297096 |
98847 | 65459 | 230555 | 266050 | 147445 | 61572 | 130242 | 294574 | 99309 | 194915 | 74363 |
138020 | 157729 | 228079 | 187175 | 120139 | 245111 | 250824 | ||||
6000 | 2400 | 5000 | 3000 | 15204 | 54845 | 128298 | 77234 | 27357 | 135285 | 49600 |
25501 | 12634 | 45500 | 35643 | 81082 | 41729 | 96457 | 39847 | 85983 | 144709 | 166288 |
41015 | 28739 | 19985 | 45193 | 78132 | 62129 | 99740 | 53909 | 84563 | 180612 | 120414 |
23350 | 31356 | 47093 | 65264 | 16515 | 128937 | 120063 | 54371 | 123171 | 178675 | 152874 |
46395 | 43043 | 102609 | 73468 | 122811 | 150319 | 213312 | 87502 | 123967 | 199604 | 157062 |
63195 | 54120 | 104405 | 64786 | 70059 | 63449 | 117956 | 91641 | 141536 | 236075 | 207570 |
2906 | 32620 | 52577 | 94000 | 197556 | 251541 | |||||
60820 | 107898 | 94860 | 75259 | 95609 | 255686 | 169934 | 97019 | 243613 | 176183 | 402386 |
47415 | 77222 | 58209 | 51934 | 93917 | 107565 | 143483 | 74107 | 122304 | 172936 | 111278 |
83909 | 49844 | 44174 | 110709 | 140759 | 130432 | 181711 | 144496 | 261847 | 590516 | 454397 |
47422 | 76923 | 139798 | 87751 | 216183 | 212816 | 387756 | 263975 | 426260 | 700925 | 739109 |
183126 | 42077 | |||||||||
80492 | 55564 | 61584 | 78388 | 126327 | 232226 | 134540 | ||||
2000 | 10800 | 8160 | 2000 | 17200 | 17100 | 37200 | 26300 | 38400 | 37000 | 44500 |
3900 | 36835 | 34385 | 34960 | 133899 | 34900 | 53653 | 14102 | 47042 | 44575 | -198 |
6160 | 19926 | 15037 | 38346 | 112961 | 53604 | 123037 | 28593 | 15692 | 56534 | 6840 |
28537 | 9900 | 50779 | 92772 | 120164 | 113419 | |||||
13200 | 9800 | 19500 | 7975 | 16246 | 29587 | 25690 | 34248 | 59651 | 67521 | 36931 |
39660 | 49200 | 65860 | 57100 | 59452 | 79125 | 81966 | 88149 | |||
77855 | 92384 | 185562 | 95194 | 178158 | 190246 | 243826 | 28504 | 49980 | 97740 | 64910 |
68578 | 68809 | 80125 | 107128 | 90110 | 102518 | 192023 | 110614 | 101750 | 300740 | 207930 |
34299 | 57764 | 44615 | 57313 | 93535 | 71652 | 119180 | 66983 | 141735 | 136364 | 65943 |
51812 | 60378 | 41707 | 41078 | 140767 | 87112 | 116953 | 54972 | 86318 | 421715 | 92064 |
31784 | 61821 | 46560 | 58937 | 71557 | 136211 | 155913 | 40353 | 76152 | 188431 | 205881 |
17420 | 36145 | 98968 | 32803 | |||||||
77058 | 154926 | 239451 | 137602 | |||||||
8932 | 61534 | 71790 | 79650 | 75188 | 85679 | 165205 | 147556 | 138004 | 222390 | 186736 |
Can you give an example of what the output should look like?
Dear Ballardw,
My output looks like this
i need to do 5 groups using 10 percentiles like 1-2 is group 1 - - - 8-10 is group 5
Inc2001 | Income Group2001 | Inc2002 | Income Group2001 |
5400 | 1 | 17800 | |
22300 | 3 | 22500 | |
52000 | 2 | 68000 | |
20000 | 5 | 138000 | |
5000 | 4 | 6150 | |
35600 | 1 | 21100 | |
10000 | 2 | 12900 | |
11000 | 4 | 9750 | |
19200 | 1 | 10500 | |
3000 | 2 | 8110 | |
59600 | 1 | 30000 | |
18400 | 29025 | ||
30000 | 21000 | ||
113743 | 136377 | ||
52645 | 81526 | ||
-12089 | 24526 | ||
-34462 | 218391 | ||
-16741 | 120024 |
10000 | 12900 | |||||||||
19200 | 10500 | |||||||||
59600 | 30000 | |||||||||
18400 | 29025 | |||||||||
30000 | 21000 | |||||||||
113743 | 136377 | |||||||||
52645 | 81526 | |||||||||
-12089 | 24526 | |||||||||
-34462 | 218391 | |||||||||
-16741 | 120024 | |||||||||
This looks more like a format problem to me than an array problem, though you could probably do it with temporary arrays.
How big is your data? Flipping the data to a structure such as Year Income is probably easier to code, but could become too big.
How many years do you have and do you need to do this repeatedly or just a one off?
Here is my thoughts:
1. Create formats for all the years, name them like f2001pct, f2002pct, etc.
2. In data step setting 3 arrays
array inc (20) inc2001 inc2002.....;
array fmt (20) $ 10 _temporary_ ("f2001pct." "f2002pct." ..........); /* the . needs to be included in the quotation marks */
array flag (20) group2001 group2002.........;
do i = 1 to 20;
flag(i)=putn(inc(i), fmt(i));
end;
Hope this helps!
Mind that PUTN works run time, so it can be a bit slow. I used INPUTN in the same fashion on 8 million rows with another 5 variable array loop within this flag loop on Unix, it took a few hours to run.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.