DATA Step, Macro, Functions and more

in-line view subquery

Accepted Solution Solved
Reply
Super Contributor
Posts: 271
Accepted Solution

in-line view subquery

[ Edited ]

I have included here an in-line view examle  in Proc  Sql (left join of three tables).  What are the most common uses of it?

 

data test1;
input id1 x;
cards;
1 3
2 5
3 6
;
run;

data test2;
input id2 y;
cards;
1 7
2 9
4 4
;
run;

data test3;
input id3 z;
cards;
1 8
3 9
4 2
;
run;


proc sql;
  select a.x,b.y,c.z
        from (select a.x,b.y 
                   from test1 a
				       left join test2 b
					     on a.id1=b.id2)
		left join test3 c
		  on a.id1=c.id3;
  quit;
run;

Accepted Solutions
Solution
‎01-04-2016 11:34 PM
Super User
Posts: 19,768

Re: in-line view subquery

Posted in reply to SAS_inquisitive

That's a fairly broad question...

 

I'll shoot back, your in line query doesn't add value. You can accomplish the same thing directly via:

 

proc sql;
create table want2 as
  select a.x,b.y,c.z
from test1 a
left join test2 b
on a.id1=b.id2
left join test3 c
 on a.id1=c.id3;
  quit;

Typically, I think In line queries are used when you want to add in some data that was summarized already so you'll see a join with something like date=min(date) to get the first record of an event . It's also when you need to look up information for a specific field in a table but need to summarize that information first somehow, so you'll see select sum(a.x) as sum_x in the subquery.  As always, there are other ways to accomplish these tasks beside inline queries.

View solution in original post


All Replies
Solution
‎01-04-2016 11:34 PM
Super User
Posts: 19,768

Re: in-line view subquery

Posted in reply to SAS_inquisitive

That's a fairly broad question...

 

I'll shoot back, your in line query doesn't add value. You can accomplish the same thing directly via:

 

proc sql;
create table want2 as
  select a.x,b.y,c.z
from test1 a
left join test2 b
on a.id1=b.id2
left join test3 c
 on a.id1=c.id3;
  quit;

Typically, I think In line queries are used when you want to add in some data that was summarized already so you'll see a join with something like date=min(date) to get the first record of an event . It's also when you need to look up information for a specific field in a table but need to summarize that information first somehow, so you'll see select sum(a.x) as sum_x in the subquery.  As always, there are other ways to accomplish these tasks beside inline queries.

Super Contributor
Posts: 271

Re: in-line view subquery

@Reeza So you mean like this code, that will join a summary data to every observation of some other data set ?

         

if _N_=1 then 
set some_summary_data ;
set some_other_data_set;
Super User
Posts: 19,768

Re: in-line view subquery

Posted in reply to SAS_inquisitive
Possibly, like I mentioned it's a broad question. I would use a cross join in that case. As a way to replicate the first/last processing is a better example. How would you implement a first/last logic from data step in SQL?

What answer are you looking for? Here's a set of X scenario's to use an In Line Query? Part of this is experience, which people can share, and part of this is preference. I hate in line queries and prefer to generatetemp tables for clarity. Efficiency isn't usually a huge part of my programming tasks.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 224 views
  • 1 like
  • 2 in conversation