p1 add p2 p3 pkey 1 xyz 1 1 a . lkj 5 5 b 6 mnb 7 . c 8 klm . . d . sdf 10 . e As from where the pan is coming for the pkey that pan last digit should append to the primary key output p1 add p2 p3 pkey 1 xyz 1 1 a_1 1 xyz 1 1 a_2 1 xyz 1 1 a_3 5 lkj 5 5 b_2 5 lkj 5 5 b_3 6 mnb 7 . c_1 7 mnb 7 . c_2 8 klm . . d_1 10 sdf 10 . e_2 ... View more

But art but while coming to the output above i have not seen the household id 2 and 5 are mising... Then i have changed the macro intial household id to 1then i got this output..   %let stopthis=1;   /*Assign initial household numbers*/ the ouput i got is ...    Obs    pan1    add1    pan2    pan3    household                                             1    aaa     bbb     ccc     ddd         1                                                2    qqq     rrr     www     aaa         1                                                3    rrr     ppp     mmm     lll         1                                                4    uuu     zzz     ffff    ppp         1                                                5    p       l       m       n           2                                                6    m       aaa                         2                                                7    a       b       c                   3                                                8    jjjj    eee     rrr     ooo         1                                                9                                        4                                               10    e       f                           5                                               11    a       e                           3                                               12    g       h                           6                                               13    i       j       k                   7                                               14    i       j       k       a           7                                               15    sss     www                         1                                               16                            eee         1    ... View more

proc sql;  update temp1 a set clusterid= (select min(b.clusterid) from temp2 b where a.pan=b.pan) where a.pan ne " " ; quit; Got it  ... View more

output a b c d cluster_id 1 2 3 4  1   *the clster id is 1 as it is the intial one* 2 3 4 1  1   *the clster id is 1 as 2 3 4 are there in clusterid 1* 5 6 7 8  2   *the clster id is 2 as it is no combitantions of 1 * 10 11 12 1 1  *the clster id is 1 as one is matching form the cluster id *            3              *the clster id is 3 as it is all blank *            4             *the clster id is 4 as it is all blank * 89 78 23 3 1     *the clster id is 1 as 3 is matching form the cluster id 1 so again assained cluster 1 for this  * 90 91 92 93 5    *the clster id is 5 as it no relation with the above ones* ... View more

output a b c d cluster_id 1 2 3 4  1   *the clster id is 1 as it is the intial one* 2 3 4 1  1   *the clster id is 1 as 2 3 4 are there in clusterid 1* 5 6 7 8  2   *the clster id is 2 as it is no combitantions of 1 * 10 11 12 1 1  *the clster id is 1 as one is matching form the cluster id *            3              *the clster id is 3 as it is all blank *            4             *the clster id is 4 as it is all blank * 89 78 23 3 1     *the clster id is 1 as 3 is matching form the cluster id 1 so again assained cluster 1 for this  * 90 91 92 93 5    *the clster id is 5 as it no relation with the above ones* ... View more

Thqs it worked but when i added set statement for the blank nubers the no is not generatring data temp1 (drop=citie: number); set temp;   array cities(99999) $;   retain cities;   if city ne "" then do;     if whichc(city,of cities ) gt 0 then      no=whichc(city,of cities );     else do;       number+1;       cities(number)=city;       no=number;     end;   end;   else do;     number+1;     no=number;   end; run; proc print; run; But i want the no for blank values also..                                                          output:                                                         Obs    city      no                                                          1     hyb        1                                                          2     vja        2                                                          3     nlg        3                                                          4     mum        4                                                          5     pune       5                                                          6     pune       5                                                          7     hyb        1                                                          8     vja        2                                                          9     triven     6 ... View more