Consider the common SQL task of counting the count(*) over a select group.
data have;
length s $1 t $3; format id 1. ;
do id = 1 to 4;
do rep = 1 to id;
s = cats (id);
t = 't_' || cats (id);
output;
end;
end;
run;
proc sql; select id, s, count(*) as freq format=4. from have group by id, s;
id s freq
-----------
1 1 1
2 2 2
3 3 3
4 4 4
Now add the twist that one of the selects is mapping a non-select column t as select column s.
So, there is now some ambiguation of what s is. Is it the original want.s or the as s.
* s in the group is the non-select s and causes automatic remerging; proc sql;
select 'way 1' as way, id, t as s, count(*) as freq format=4. from have
group way, id, s;
way id s freq
------------------------
way 1 1 1 other 1
way 1 2 2 other 2
way 1 2 2 other 2
way 1 3 3 other 3
way 1 3 3 other 3
way 1 3 3 other 3
way 1 4 4 other 4
way 1 4 4 other 4
way 1 4 4 other 4
way 1 4 4 other 4
Grouping by column numbers disambiguates the role of t as s
proc sql; select 'way 2' as way, id, t as s, count(*) as freq format=4. from have
group 1, 2, 3;
way id s freq
--------------------
way 2 1 t_1 1
way 2 2 t_2 2
way 2 3 t_3 3
way 2 4 t_4 4
Selecting t as new column s_new also disambiguates
proc sql; select 'way 3' as way, id, t as s_new, count(*) as freq format=4. from have
group way, id, s_new;
way id s_new freq
----------------------
way 3 1 t_1 1
way 3 2 t_2 2
way 3 3 t_3 3
way 3 4 t_4 4
Trying to remain in the context of column names, calculated s causes an ERROR
proc sql; select 'way 4' as way, id, t as s, count(*) as freq format=4. from have
group way, id, calculated s;
ERROR: The following columns were not found as CALCULATED references in the immediate query: s.
My code will have to go the 1,2,3 way.
Regardless, is there a syntax modification to have the as s column in the group clause using the column name s (in other words something like calculated s that does not cause an ERROR)
... View more
