Hello everyone,   I have a scenario where I need to find whether the same "reward_program_id1" OR "reward_program_id2" is used in multiple product_id. In the below data product_id is unique however, reward_program_id1 & reward_program_id2 digits can be used interchangeable to purchase product.    For example,  product_id 89067 is purchased using reward_program_id2 = 567694(row #1)  and the same digits are used as reward_program_id1 to purchase product_id 90873 (row #4).  Likewise,  product_id 34340 is purchased using reward_program_id1 = 429008(row #3)  and the same digits are used as reward_program_id2 to purchase product_id 10983(row #8).   In the output I am trying to add a column "Multi_program_id" Y/N if the same reward ID is used in more than one product. I hope this makes sense, any help is appreciated! Thanks in advance.    Data have; input product_id product_delivered $ reward_program_id1 reward_program_id2 ; infile datalines delimiter=','; datalines; 89067,Y,223456,567694 58394,N,343412, 34340,Y,429008,412948 90873,N,567694,293832 91028,N,389292,902221 38034,N,459303,343033 76281,N,828292,282929 10983,Y,909033,429008 18738,N,929292, 17930,Y,343412,982932 29383,N,898903, 11283,Y,976409,829737 98203,N,839234,928302 12930,N,918833,303033 78023,Y,123233,412948 ; run;   Output:   Product_id product_delivered reward_program_id1 reward_program_id2 Multi_program_id 89067 Y 223456 567694 Y 58394 N 343412 . Y 34340 Y 429008 412948 Y 90873 N 567694 293832 Y 91028 N 389292 902221 N 38034 N 459303 343033 N 76281 N 828292 282929 N 10983 Y 909033 429008 Y 18738 N 929292 . N 17930 Y 343412 982932 Y 29383 N 898903 . N 11283 Y 976409 829737 N 98203 N 839234 928302 N 12930 N 918833 303033 N 78023 Y 123233 412948 Y   ... View more

Hello, I need help with generating a repot in tabulate form. Below is my input data and  the format of the output that I am looking to generate. I tried few iterations using proc tabulate but could get the expected output. Any help is much appreciated. Thank you in advance for your time and attention!!     Code:   Data sample; input P_date date9. week_day $ Merchant $ ct pin_ct avg_days; cards; 02Jan2020 Saturday CCL 77 23 13.987 02Jan2020 Saturday MMY 7034 290 17.0566 02Jan2020 Saturday HGY 897 156 56 05Jan2020 Tuesday CCL 976 85 19.874 05Jan2020 Tuesday MMY 6756 442 89.9843 05Jan2020 Tuesday HGY 900 11 15.77 05Jan2020 Tuesday JHU 8900 654 78.7655 ; run; Data Sample2; Set sample; format p_date date9.; run;   Output:       Product Sale Report     CCL MMY HGY JHU P_dt Week_day ct Pin Ct Pin % Avg Days ct Pin Ct Pin % Avg Days ct Pin Ct Pin % Avg Days ct Pin Ct Pin % Avg Days 02JAN2020 Saturday                    77                 23 29.87% 13.987         7,034            290 4.12% 17.0566            897            156 17.39% 56 - - - - 05JAN2020 Tuesday                  976                 85 8.71% 19.874         6,756            442 6.54% 89.9843            900               11 1.22% 15.77          8,900             654 7.35% 78.7655 Total              1,053              108 10.26% 16.9305      13,790            732 5.31% 53.52045         1,797            167 9.29% 35.885          8,900             654 7.35% 78.7655 ... View more

Hello, I have a dataset called "HAVE" which has 8 phone number columns for each prod ID. There are some invalid phone numbers in this dataset that I want to remove. I also have a list of invalid phones numbers and I am calling it as "invalid_numbers". Basically I want join my "have" against "invalid_numbers" on ID and phone number and if a match found I want to remove that phone number from the have datasaet for that specific ID. I am new to hashing/array concept and I not getting my desired output. Not sure what I am doing wrong and couldn't it figure out.   Could someone please help? Thanks. Data have; input @1 ID :$4. @6 tp1 $10. @17 tp2 $10. @28 tp3 $10. @39 tp4 $10. @50 tp5 $10. @61 tp6 $10. @72 tp7 $10. @83 tp8 :$10. ; infile cards missover ; cards; 1325 5872584458 2478569877 5489 8658656767 6048795248 5478 2358984155 8799 4897568698 3697489567 ; run; Data invalid_numbers; input ID :$4. inv_phn $10.; infile cards; cards; 1325 5872584458 8799 3697489567 2578 7879565688 3287 9831765765 8974 2478569877 9876 2579375824 ; Quit;   Output:   ID tp1 tp2 tp3 tp4 tp5 tp6 tp7 tp8 1325   2478569877             5489 8658656767     6048795248         5478 2358984155               8799 4897568698                 data want; set have; array phn tp1-tp8; if _N_=1 then do; dcl hash H(dataset:'invalid_numbers'); H.definekey('ID','inv_phn'); H.definedone(); if 0 then set invalid_numbers; end; do over phn;  if H.find() = 0 then call missing(phn); end; drop inv_phn; run;   ... View more

Hello, I receive data from vendor which looks something like below. It has 8 phone number columns(char) from tp1 to tp8 and there are could be missing phone numbers in between for each ID. For instance, I could get a record where tp1 & tp8 columns are populated populated and rest of the phone columns are blank.  Data have; input @1 ID :$4. @6 tp1 $10. @17 tp2 $10. @28 tp3 $10. @39 tp4 $10. @50 tp5 $10. @61 tp6 $10. @72 tp7 $10. @83 tp8 :$10. ; infile cards missover ; cards; 1325 5872584458 2478569877 1325 5872584458 3697489567 5489 8658656767 6048795248 5478 2358984155 5478 2358984155 8799 4897568698 3697489567 8799 4897568698 3697489567 ; run; I want an output that captures all the phone numbers in a single column and dedup them.   Output: tp 5872584458 2478569877 3697489567 8658656767 6048795248 2358984155 4897568698 3697489567   Thanks in advance for your help!! ... View more