Hi,   Can someone help me to write below excel formula in SAS. a2 has "First Number" and some other values like First, second, third, Third Number, etc. b2 has some values like '1234' these are char format and Names a:b has '1234' and something like '1346'  chat format and 2 is Yes/No b2 has some values like '1234' these are char format and Names a:b has '1234' and something like '1346'  chat format and 2 is Yes/No  c2 is also char format and a values from matching sheet will matching with c2 values and 2 is Yes/No   =if(right(a2,6)="Number","no", if(iferror(vlookup(b2,'Names'!$a:$b,2,false),"no")="yes","no", if(iferror(vlookup(b2,'Location'!$a:$b,2,false),"no")="yes","no", vlookup(c2,'Matching'!$a:$b,2,false))))   Main data A B C First 1234 1A First Number 1348 11A Second Number 1236 B4 Second 1345 B7   Names data A B 1234 No 1348 Yes 1236 Yes 1345 No   Location data  A B 1234 No 1348 Yes 1236 Yes 1345 No   Matching data A B 1A Yes 11A No B4 No B7 Yes   Thanks, vnreddy ... View more

Hi, Can someone help me with below issue.  When i use sum and group by in proc sql with or without distinct, i am not getting unique records. As you can see from below output image, i should only get 3 rows in my output. How can i get rid of 2nd or 3rd row from my output. data have; input code $1-3 tcode $5-8 order_date :date9. invoice_date :date9. invoice_code $30-33 customer $35-39 Cust_code $41-44 P_code $46-50 P_name $52-60 H_code $62-65 T_code $67-72 Plant $74-77 Cat_code $79-81 Cat_desc $83 Item_code $85-87 Item_desc $89-98 Distance 100-102 Qty 104-107 HPrice 109-112 Rev 114-119; format order_date date9. invoice_date date9.; ; datalines; 101 1019 21NOV2023 25NOV2023 2626 B11AC B100 52486 New_B11AC EAST GNA1UC 100A 200 H 380 HPayDry 9.3 0 0.00 0 101 1019 21NOV2023 25NOV2023 2626 B11AC B100 52486 New_B11AC EAST GNA1UC 100A 200 H 370 HChargeDry 9.3 2.14 0.00 94.25 101 1019 21NOV2023 25NOV2023 2626 B11AC B100 52486 New_B11AC EAST GNA1UC 100A 200 H 370 HChargeDry 9.3 2.14 0.00 113.36 101 1019 21NOV2023 25NOV2023 2626 B11AC B100 52486 New_B11AC EAST GNA1UC 100A 114 S 520 0/2_catP3 9.3 2.14 4.35 169.38 ; proc sql; create table want (drop=Qty Rev) as select*, sum(Qty) as P_Qty, sum(Rev) as Revenue from have group by cat_code, Cat_desc, item_code,item_desc ; quit; current output what i am getting    Expected output when i use sum and group by i should get only 3 rows as per the requirement. Row 2 & 3 are same, how should i get rid of one row.   Sum issue : for sample purpose i have manually shown few records here, when i use the sum function on a large dataset i end getting a sum on complete data rather then group by. Below image shows an issue with sum on revenue and qty when i use a sum function and group by in proc sql. In reality it should only give the sum based on grouping. Below sum is not right, it won't be this high number.       Thanks, vnreddy ... View more