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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.