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;
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).
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;
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.
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.
Thank you responding. It works!
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).
Thank you for responding. It works!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.