Desktop productivity for business analysts and programmers

proc sql Create Table without 'As' query expression

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

proc sql Create Table without 'As' query expression

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;


Accepted Solutions
Solution
‎10-03-2016 01:00 PM
Respected Advisor
Posts: 3,840

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

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).

 

View solution in original post


All Replies
Grand Advisor
Posts: 10,238

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

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

proc sql;

<first query>

<second query>

<third query>

quit;

Frequent Contributor
Posts: 104

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

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.

9-30-2016 10-49-09 AM.png

Respected Advisor
Posts: 4,609

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

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.

PG
Frequent Contributor
Posts: 104

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

Thank you responding.  It works!

Solution
‎10-03-2016 01:00 PM
Respected Advisor
Posts: 3,840

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

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).

 

Frequent Contributor
Posts: 104

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

Thank you for responding.  It works!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 436 views
  • 0 likes
  • 4 in conversation