BookmarkSubscribeRSS Feed
yags
Calcite | Level 5

Hi there

I am very new to SAS and have only just started using SAS Enterprise guide

I work in a company where most of our external/financial reporting is prepared in Excel. We are working on a SAS based solution for some of our reporting

I understand that SAS is a statistical software but it also has capabilties such as getting data from datawarehouses which is quite helpful

We are trying to build Proc Sql queries for getting data from data repositories and aggregating the data into a reportable format

One of the troubles that we are facing is that Proc Sql code needs to copied over to a different project each time there is a new reporting requirement that is slightly different from the previous report

Is there a way of stroing these proc sql queries in a library of some sort? I know that SAS macros should be able to allow code reuse. But just wondering if there is anything else that will allow reuse?

I hope whatever I have written makes sense Smiley Happy

Any help from you guys will be greatly appreciated

Thanks in advance

5 REPLIES 5
Reeza
Super User

Two options that come to mind:

1. Make the query general and create views in your database that can be then queried directly.

2. Use %include to point to program and include it in your scripts.

Patrick
Opal | Level 21

You can store the query in a macro - and if it uses the SAS Autocall facility the you can use this macro in any of your projects. SAS(R) 9.4 Macro Language: Reference

You could create a view and store this view in a permanent pre-assigned SAS library - or directly in the data base.

You could store your SQL code in a separate .sas file and then use it via an %include statement.

In general: If you want to build up some sort of reporting system then I would actually use EG for development/maintenance of such reports but I would store all productionised code in separate .sas files. You can always use such .sas files within your EG project either via directly opening them or via %include statements.

What you want to avoid is having multiple versions of almost the same in multiple complex EG project as this will become sooner or later a maintenance "nightmare".

jakarman
Barite | Level 11

Using Eguide you may have a Server based approach with the SAS metadata and more.

Tools in that approach are metadata-based for building your queries that way.   Build it as SP'-s and that building blocks will become central managed.

When you do not have that with SAS metadata, Eguide has a query builder to generate the (ansi SQL) queries. This works well with simple queries. the difficult  ones can become more cumbersome when you have exterenal DBMS-systems involved.
Organizing code in a way reuse is more easy is a classic ICT question.
- modular programming, Objected oriented, Service oriented

- Version management, release managemen, Develop-Test-UAT-Production (DTAP)

SAS is having many ways to code, question would make more sense as: how is your ICT being organized?

---->-- ja karman --<-----
ChrisHemedinger
Community Manager

If you want to simply re-use the query definition among multiple projects, look at the Query Template feature.  This allows you to take a query you've defined and "register" it as a re-usable template that will then appear in your task list/menus.  This feature is the underpinning for building subqueries with EG, described in this post:

Building an SQL subquery in SAS Enterprise Guide - The SAS Dummy

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
yags
Calcite | Level 5

Thank you all for the replies

The options suggested look helpful indeed

As mentioned earlier in my post, I am very new to SAS and will have to go thorugh each option suggested one by one Smiley Happy

your responses very reassuring and are much appreciated

Thanks again

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1958 views
  • 8 likes
  • 5 in conversation