I wonder how to make use of the existing array to create another array. thanks   %macro test(varlist) data test; set test; array var_arr (*) &varlist; array var_arr2 (*) &varlist_1; run;  but it did not work... I want to declare the following with var_arr(*): array var_arr2 (*) a_1 b_1 c_1; thank you very much   ... View more

data mydata; input Category $ Item type amount; datalines; A 1 100 11111 A 2 800 11111 A 3 123 11111 B 1 113 11111 B 2 800 11111 C 1 111 11111 C 2 800 11111 ; My attempt proc sql; create table want as select *, sum(amount and item <> 900) as without900, sum(amount) as total from mydata group by category ; quit; Result Category Item type amount without900 total A 3 123 11111 3 33333 A 1 100 11111 3 33333 A 2 900 11111 3 33333 B 2 900 11111 2 22222 B 1 113 11111 2 11111 C 2 900 11111 2 11111 C 1 111 11111 2 11111 Expected result Category Item type amount without900 total A 3 123 11111 22222 33333 A 1 100 11111 22222 33333 A 2 900 11111 22222 33333 B 2 900 11111 11111 22222 B 1 113 11111 11111 11111 C 2 900 11111 11111 11111 C 1 111 11111 11111 11111 I know this can be easily achieved by creating another table and maybe hence using left join. I wonder how to achieve the expected using as least proc SQL step as possible. Thank you very much.   ... View more

My dataset data mydata; input Category $ Item $; datalines; A 1 A 1 A 2 B 3 B 1 ; My attempt proc sql; create table mytable as select *, count(Category) as Total_No_in_Category, count(Category)-count(item, "3") as No_of_not_3_in_the_same_category from mydata group by Category; run; Result Category No_of_not_3_in_the_same_ category Total_No_in_Category A 3 3 A 3 3 A 3 3 B 2 2 B 2 1   My expected result Category No_of_not_1_in_the_same_ category Total_No_in_Category A 2 3 B 1 2   I wonder how to achieve the expected result using only proc SQL. Thank you so much. ... View more