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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.