Hello,   I have a dataset called "coupon_data" which has 5 coupon code columns for each prod ID.  There are some invalid coupon code in this dataset that I need to remove. I also have a list of invalid coupon codes and I am calling it as "invalid_codes". Basically I want compare my "coupon_data" against  "invalid_codes" and if match found I want to remove that code from the coupon column.    I am fairly new to hashing/array concept and i am sure you can judge that from my below experimental code. I know i am doing something wrong but i couldn't figure out  and I am not getting the desired output.   Could someone please help? Thanks.     Data coupon_data; input @1 prod_ID :$6. @8 coupon1 $5. @14 coupon2 $5. @20 coupon3 $5. @26 coupon4 $5. @32 coupon5 $5.; infile cards missover ; cards; 132522 KCB33 CKM90 LKJ89 LKU09 NM789 258798 OUI00 UIT78 POU89 CC909 548998 NM789 PI980 VBV46 JKO09 109743 YUO90 JJI99 POU89 MOP09 ; run; Data invalid_codes; input inv_coupon $5.; infile cards; cards; LKJ89 CC909 MOP09 YUO90 ; Quit; data valid_codes; set coupon_data; array cpn [5] coupon1-coupon5; array outcpn [5] $5; if _N_=1 then do; dcl hash H(dataset:'invalid_codes'); H.definekey('inv_coupon'); H.definedone(); if 0 then set invalid_codes; end; do i=1 to 5; coupon=cpn[i]; if H.find()=0 then do; outcpn[i]=cpn[i]; end; end; drop coupon coupon1-coupon5 i inv_coupon; rename outcpn1-outcpn5 = coupon1-coupon5; run; Desired output:   prod_ID coupon1 coupon2 coupon3 coupon4 coupon5 132522 KCB33 CKM90   LKU09 NM789 258798 OUI00 UIT78 POU89     548998 NM789   PI980 VBV46 JKO09 109743 YUO90 JJI99 POU89     ... View more

Hello, I have a input file(have.xlsx) that has ID by date. I am looking to produce summary report by date that has count of ID by different market groups(PHL,NUC,MKT,MRC,DRA). I need help with generating the desired output(results.xlsx) with totals at the end. I also want to output the data by each market group in the same file if the total of any market group is greater than zero. Basically print the rows with count greater than zero.   I am new to summary reporting so any help is greatly appreciated.   Thanks for your time! ... View more

Hello,    I am trying to produce a cross tabulation report with sum and mean values and I want my report to be generated in excel.   Here is my data: Date Week_day Sample_typ Count avg_temp 03Feb2020 Monday TYP3 2234 134.15 03Feb2020 Monday TYP1 11233 162.08 03Feb2020 Monday TYP2 109 14.08 05Feb2020 Wednesday TYP4 1245 36.19 05Feb2020 Wednesday TYP2 1056 171.13 05Feb2020 Wednesday TYP1 18 32.00 06Feb2020 Thursday TYP4 1 12.00 06Feb2020 Thursday TYP2 334 77.18 07Feb2020 Friday TYP1 1790 134.61 07Feb2020 Friday TYP4 301 71.61 07Feb2020 Friday TYP3 4509 10.74 07Feb2020 Friday TYP2 889 189.39 08Feb2020 Saturday TYP1 190 34.56                       My desired output:       I tried the below code but I am unable to insert the “Avg Temp”. Can someone please show me how to create the report in the above format? Thanks.       proc tabulate data=sample f=comma8.0 s=[foreground=black just=c]; class date date Week_day Sample_typ; var ct avg_temp; table date = 'Dt' * Week_day='Week Day' all={label='Total' s=[just=c]}, ((Sample_typ = {label ="Volume by Sample Type" s=[just=c]} ) * (ct='' * (sum=''*f=comma8.0)) ) / misstext='-'; run; ... View more