SAS Code reuse

[yags]

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

Any help from you guys will be greatly appreciated

Thanks in advance

[Reeza]

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]

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]

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?

[ChrisHemedinger]

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

[yags]

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

your responses very reassuring and are much appreciated

Thanks again