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;
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.
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.
@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;
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.