in-line view subquery

[SAS_inquisitive]

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;

[Reeza · Accepted solution]

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.

[SAS_inquisitive]

@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;

[Reeza]

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.