PROC SQL: Auto Remerge when "FROM Table vs FROM (Inline view)"
Objective: Lazy me wants to use proc sql remerge as opposed to sort and DOW// proc summarycount and merge back
Here is the testdata I took from another thread used by draycut and Ksharp to experiment for my case:
data testdata(drop=i j);
array vars var1-var3;
do i=1 to 100;
do j=1 to dim(vars);
vars[j]=rand('integer', 1, 10);
end;
output;
end;
run;
The expected-->
Get the count of each by group
proc sql;
Create table the_expected as
select *, count(*) as c
from testdata
group by var1,var2,var3
order by var1,var2,var3;
quit;
Yeah right, so traditional and merge back with the original to get accomplish the objective.
Intent: Take advantage of auto emerge and why bother coding the second pass:
But since there is no open scope for autoremerge for the reason var1,var2 and var3 are the only variables in testdata that also forms the group by combination, a way out to force remerge would be is by having temp variable in select clause and then drop using dataset option
Cool as it seems, I thought, let me try
proc sql;
Create table the_expected(drop=j) as
select *,0 as j, count(*) as c/*purpose of j is to force remerge*/
from testdata
group by var1,var2,var3
order by var1,var2,var3;
quit;
However, my understanding was obviously incorrect. The precedence is select columns and group by and vice versa seem to go hither and wither. This also makes sense to why users many a times prefer datastep sequential processing if not direct access with point= or whatever that gives greater control.
Anyways, Either way, I still need the temp var to force remerge , so how about an inline view?
proc sql;
Create table inline_view_attempt(drop=j) as
select *,count(*) as c
from (select *,0 as j from testdata)/*purpose of j is to force remerge*/
group by var1,var2,var3
order by var1,var2,var3;
quit;
hmm, idea seems reasonable, but unfavorable result yet again
So, now what if I had the temp variable created in another query and attempt to read a table rather than a inline view-->
proc sql;
/*temp to hold the extra var j to force remerge*/
create table temp as
select *,0 as j from testdata;/*purpose of j is to force remerge*/
/*query the temp table that has the temp var j */
Create table From_temp_table_attmpt(drop=j) as
select *,count(*) as c
from temp
group by var1,var2,var3
order by var1,var2,var3;
quit;
This works fine but completely defeats the intent and objective.
Can somebody help me understand the nitty gritty of this behavior?
PS
Above all, the *, remerge et al in general are some features I love about proc sql and I believe I have been using proc sql quite effectively and if can get the understanding to have my mind and fingers to have control over it, all the more the better. Thank you!
