BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_inquisitive
Lapis Lazuli | Level 10

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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
Lapis Lazuli | Level 10

@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
Super User
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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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