topic Re: proc sql Create Table without 'As' query expression in SAS Enterprise Guide

proc sql Create Table without 'As' query expression

sdang — Fri, 30 Sep 2016 17:22:30 GMT

Hi all,

I have four proc sql create table statements, each succesive statement depends on the previous one. What I want to do is clean this up. I don't want it to show all three previous proc sql statement as a query expression but just the final one. This way the Process Flow workspace does not show three queries but just the final one that i need. Is there a statement that i can use that goes something like this:

proc sql;

create table work.query_For_TimeSegmentation_SUM as "Not Show"

The "Not Show" don't create a table?

Below are my proc sql codes:

proc sql;

create table work.query_For_TimeSegmentation as

select t1.PlayerID,

t1.'Time Segmentation'n,

(COUNT(DISTINCT(t1.DateKey))) as 'Count Distinct_of_Datekey'n

from WORK.FILTER_FOR_QUERY_FOR_FACTSLOTS t1

group by t1.PlayerID,

t1.'Time Segmentation'n

order by t1.PlayerID;

quit;

proc sql;

create table work.query_For_TimeSegmentation_SUM as

select t1.PlayerID,

SUM('Count Distinct_of_Datekey'n) as 'SUM_Trips'n

from work.query_For_TimeSegmentation t1

group by t1.PlayerID

order by t1.PlayerID;

quit;

proc sql;

create table work.query_For_TimeSegmentation_Join as

select t1.PlayerID,

t1.'Time Segmentation'n,

t1.'Count Distinct_of_Datekey'n,

t2.'SUM_Trips'n,

(t1.'Count Distinct_of_Datekey'n/t2.SUM_Trips) format=percent8.3 as '%TimeSegment'n

from work.query_For_TimeSegmentation t1

left join work.query_For_TimeSegmentation_SUM t2 on (t1.PlayerID = t2.PlayerID);

quit;

Re: proc sql Create Table without 'As' query expression

ballardw — Fri, 30 Sep 2016 17:31:55 GMT

You can put all of that as a single Proc SQL call in a code node.

proc sql;

quit;

Re: proc sql Create Table without 'As' query expression

sdang — Fri, 30 Sep 2016 17:53:09 GMT

Hi, thank you for responding. what you provided helps, but not what i wanted. In the screen shot below, i don't want

the first four queries table to show on the Process Flow map. I just want the bottom one. These are create by using proc sql create table.

Re: proc sql Create Table without 'As' query expression

PGStats — Sat, 01 Oct 2016 00:49:31 GMT

You don't have to create explicit tables if you don't need them. You can use subqueries:

proc sql;
create table A as
select ....
from
	(select .... 
	 from 
		( select id, ....
		  from B
		  group by .... ) inner join
		( select id, ....
		  from C
		  group by .... ) on B.id = C.id
	 group by .... )
group by ....
order by ....;
quit;

Note: Do not include an ORDER BY clause in a subquery.

Re: proc sql Create Table without 'As' query expression

Patrick — Sat, 01 Oct 2016 01:56:14 GMT

In case you just don't want the intermediary tables to show up in the EG flow, delete the intermediary tables once you've got your final table (using a SQL DROP, or Proc Datasets delete or Proc Delete).

Re: proc sql Create Table without 'As' query expression

sdang — Mon, 03 Oct 2016 16:58:17 GMT

Thank you responding. It works!

Re: proc sql Create Table without 'As' query expression

sdang — Mon, 03 Oct 2016 16:58:38 GMT

Thank you for responding. It works!