At Veramed, we encourage our team to explore and develop techniques that could help increase efficiencies within the clinical trial process and share these insights internally and externally.
In this Q&A, we talk with Hector Leitch, a Programmer II at Veramed, about his 2021 PHUSE US Connect presentation exploring intuitive ideas beyond the Basics of PROC SQL. Structured Query Language (SQL) is a popular programming language for retrieving and updating data in tables, while PROC SQL is the SQL implementation within the SAS System. Hector discusses the context and motivations for his talk, and we share a link to the slides with code examples for those who may have missed the presentation.
What interests you about PROC SQL?
As a Programmer, I’ve always found the SQL language especially easy to use as it closely reflects how I think about datasets as a mathematician. When I started learning SAS, it was quite an adjustment to get used to the data step and record-by-record handling of data. By contrast, PROC SQL handles data in a way that deals more directly with the structure of the data. For example, when you merge datasets in PROC SQL, you specify a condition/rule and records are merged together when that condition is satisfied; this seems much more flexible and generalised than the data step merge, which can only merge by matching values between data sets. I think part of what has made it easy to learn is that the syntax of PROC SQL results in queries that can be read like sentences describing what is happening.
What aspects did you cover in your presentation?
I wanted to share some practical programming tips for programmers with an introductory experience level to illustrate what’s possible with PROC SQL; something that is impressive and beyond the basics, without being off-putting. My presentation covers three ‘tricks’, each with simple, workable examples in the context of programming ADaMs. In my experience, this purpose is especially well suited to PROC SQL, as we can make use of the structures and patterns found within SDTM.
How would you like your ideas to inspire others?
I hope that people see the potential when looking at the amount of code required for these tricks compared with what it can accomplish. I’ve intentionally chosen common and realistic examples that occur in ADaM programming, so that only a basic understanding of PROC SQL is required for someone to take the examples and use them in their own code. Beyond this, all the tricks I show are highly flexible and can be adapted in a wide range of scenarios. In particular, the PROC SQL approach to nested queries is extremely versatile; it’s simply a matter of getting up to speed with the syntax and then being creative with your application.
While I didn’t have the opportunity to present ‘live’ at PHUSE on this occasion, I did share my presentation within Veramed at one of our regular ‘Lunch and Learn’ sessions and colleagues at a range of experience levels found the tips extremely useful.
To download Hector’s slides and review his tips and tricks in PROC SQL fill out the form below.
Veramed is expanding rapidly, and we are looking for talented programmers from junior to seasoned levels to join our team. To learn more about careers at Veramed, click here.
