BookmarkSubscribeRSS Feed
TurnTheBacon
Fluorite | Level 6

I'd appreciate it if someone can please inform me of, or direct me to a place where I can learn about, best practices on when/why to use an Extract instead of an SQL Join, and vice versa. In example, if I have an Extract with a very long and complex Where-statement, is it best practise to switch to an SQL Join to make it look more orderly even though there's only one input table and no need for joins?

I ask because I remember many jobs where people have used SQL Joins instead of Extracts despite there only being one input table.

Thanks. Smiley Happy

2 REPLIES 2
RichardinOz
Quartz | Level 8

In this situation it would be my preference to use SQL rather than a datastep purely because SQL syntax is widely used and understood, by programmers from a variety of application backgrounds.  This can be important if (1) your code will be audited by a third party, or (2) if [wash your mouth!] management decides to move away from using SAS.  So unless particular datastep functionality (first. or last. processing, or arrays) I would advocate SQL if you have the choice.

Richard in Oz

LinusH
Tourmaline | Level 20

Both Extract and SQL Join uses SQL, so there is no issue here to use a data step...

As you said yourself, SQL Join comes with a lot more possibilities in the GUI, and metadata driven syntax, than Extract.

I can't say the one or the other is best practice, as long you have "ordinary" where clauses. But if you feel that the where clause builder is better than the advanced expression builder in Extract, use SQL Join.

If you intend to use more complex things like sub-query and stuff like that, SQL Join would the prefered transformation.

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 956 views
  • 6 likes
  • 3 in conversation