topic Re: proc sql: can I "group by" a variable created in select procedure? in SAS Procedures

proc sql: can I "group by" a variable created in select procedure?

xyxu — Tue, 05 Feb 2019 01:39:49 GMT

Is the following procedure legitimate? Please note that variable month is generated by the procedure itself. I can run it without any error reported, but not sure if the results are perfectly what I want.

proc sql;
	create table want as 
	select name, intnx("month", date, 0, 'e') as month format yymmdd10.,  mean(var) as var_ave
	from have
	group by name, month 
	order by name, month;
quit;

Re: proc sql: can I "group by" a variable created in select procedure?

Shmuel — Tue, 05 Feb 2019 02:36:35 GMT

You need use the descriptive word "calculated", Try

proc sql;
	create table want as 
	select name, intnx("month", date, 0, 'e') as month format yymmdd10.,  mean(var) as var_ave
	from have
	group by name, calculated month 
	order by name, calculated  month;
quit;

Re: proc sql: can I "group by" a variable created in select procedure?

xyxu — Tue, 05 Feb 2019 02:42:06 GMT

It seems that the results do not change after adding it?

Re: proc sql: can I "group by" a variable created in select procedure?

Shmuel — Tue, 05 Feb 2019 03:06:10 GMT

Please post sample of your input and its output to demonstrate where it should differ

Re: proc sql: can I "group by" a variable created in select procedure?

SASKiwi — Tue, 05 Feb 2019 03:49:37 GMT

I suspect use of the keyword - calculated - is optional in your example as both versions work without error.

Re: proc sql: can I "group by" a variable created in select procedure?

ChrisNZ — Tue, 05 Feb 2019 10:38:00 GMT

Since variable MONTH doesn't exist anywhere else, the keyword calculated can be omitted: it is obvious which MONTH you are referring to. Both queries yield the same result.

This calculated keyword is a powerful addition to ANSI SQL made by SAS.

Now if only SAS added other useful language extensions to SQL...