topic Re: Automated Replacement of View with Table in SAS Programming

Automated Replacement of View with Table

Patrick — Mon, 25 Jan 2021 22:46:10 GMT

Hi guys,

Does anyone know of an option or other mechanism - documented/supported or not - that makes a recent SAS version replace views with same named tables.

What I would like to be able to do is to execute code as below.

proc sql;
  create view class as
  select *
  from sashelp.class
  ;
quit;


/* data step or SQL create table */
data class;
  set sashelp.class;
run;

Background

I'm working in a big environment with multiple development streams in parallel. All the code is version controlled via git.

For each new project a new branch gets created and then pulled into a project folder on the file system. For the data there is a script which creates views in the projects pointing to a single source of physical data (SAS files - data volumes are significant). So far so good and this works really well.

The challenge is: When it comes to changing and testing programs in a project then these programs create tables as output - but in the destination within the project there are now already same named views. It's not hard to just delete these views manually for unit testing but when it comes to running a whole flow for regression testing then identifying all the target tables/views and first delete them manually is rather inconvenient.

Re: Automated Replacement of View with Table

ballardw — Mon, 25 Jan 2021 23:35:17 GMT

I would be strongly tempted to examine Libraries for parallel processes or named files. Have your views in one library and the report or generated data sets in a different library. Then name collisions aren't an issue.

Another possibility would be to enforce a stronger naming pattern like all the View names start with V and the data sets with DS or similar.

Mixing types with the same name is, as you have found, a recipe for confusion and extra work if not potential disasters. If these don't fit the bill then perhaps an examination of what choices were made that are creating the situation.

Re: Automated Replacement of View with Table

ChrisNZ — Tue, 26 Jan 2021 01:06:07 GMT

>When it comes to changing and testing programs in a project then these programs create tables as output - but in the destination

Just to be clear on what's happening:

Is the issue that DEV code creates tables and PROD code uses views?

Or that input and output folders are the same in a project?

If 1) then DEV code should never write in PROD folders. Use a concatenated library (pointing to several paths) where the production paths are listed last, and are read-only. The path(s) listed first will receive all DEV files.

If 2) why is the PROD code and the DEV code different? (one creates views and one crates tables)

And why overwrite an input table with an output of the same name?

Re: Automated Replacement of View with Table

Patrick — Tue, 26 Jan 2021 05:49:40 GMT

@ChrisNZ and @ballardw

Thanks for your answers. It appears I wasn't able to fully explain the situation.

Sooo... Everything Production is under a top folder. Under this folder are sub-folders for code and data.

For a new release one creates a copy of this Production folder structure which replicates the folder structure under a Development folder. The code sub-folder contains all the production code to start with, the data sub-folder contains views pointing to the production data instead of a full copy of the production data (because the data volumes are significant). The views in the Dev area must have the same names than the tables in the Prod area for the code in the Dev area to work without any change. The views in the Dev area look like:

/* rootpath set when initiating SAS */
%let rootpath=<some path>;

/* libnames created via init macro always on top of a program */
libname staging "&rootpath/staging";

/* SQL view example. Created scripted as part of development project setup */
proc sql;
  create view staging.table_1 as
  select *
  using '<fully qualified path to prod data folder>/table_1.sas7bdat'
  ;
quit;

Above allows for multiple development projects in parallel. Using git code changes from dev branches get then merged when moving into Test.

Mostly things work already as @ChrisNZ proposes:

If 1) then DEV code should never write in PROD folders. Use a concatenated library (pointing to several paths) where the production paths are listed last, and are read-only. The path(s) listed first will receive all DEV files.

What I've done already is to create another data structure under the Dev folder - one set with the views and one set just with empty folders - and then amended then run as part of the init macro code which creates concatenated libraries (first pointing to the empty folder structure and then to the one with the views). This works as intended as long as there aren't any additional libnames in the code itself (as I want to unit test dev code that doesn't require any further changes before moving to higher environments).

I hope above explains the situation I'm facing and why I'm looking for an option which changes SAS default behaviour (not very hopeful but felt I ask before just accepting that there isn't such an option).

Re: Automated Replacement of View with Table

ChrisNZ — Tue, 26 Jan 2021 09:47:13 GMT

I still think you could use a libname pointing to (&devlib_readwrite &_prdlib_readonly) rather than creating views, no?

Re: Automated Replacement of View with Table

Ksharp — Tue, 26 Jan 2021 11:33:33 GMT

Maybe you could query the " Dictionary.views " before creating a dataset with same name.
If it exists, drop this view ?

Re: Automated Replacement of View with Table

ChrisNZ — Tue, 26 Jan 2021 20:09:10 GMT

> What I've done already is to create another data structure under the Dev folder - one set with the views and one set just with empty folders - and then amended then run as part of the init macro code which creates concatenated libraries (first pointing to the empty folder structure and then to the one with the views).

I don't understand why use views rather than use the prod folder at the end of the concatenated library.

Re: Automated Replacement of View with Table

Patrick — Wed, 27 Jan 2021 02:11:58 GMT

@ChrisNZ wrote:

> What I've done already is to create another data structure under the Dev folder - one set with the views and one set just with empty folders - and then amended then run as part of the init macro code which creates concatenated libraries (first pointing to the empty folder structure and then to the one with the views).

I don't understand why use views rather than use the prod folder at the end of the concatenated library.

True, the 2nd path in the concatenated library could directly point to the prod data - but it would be a further deviation of how stuff is configured at the site I'm working. Also: This is only a side story and doesn't address the issue with libnames in code (as I don't want to change them, add a path to them).

Re: Automated Replacement of View with Table

Patrick — Wed, 27 Jan 2021 02:16:31 GMT

@Ksharp wrote:
Maybe you could query the " Dictionary.views " before creating a dataset with same name.
If it exists, drop this view ?

I've extended the %init macro with a post process which does something similar: Get a list of all the librefs defined and use the definitions to concatenate a path to it (in first position) which points to the empty data folder structure under my project folder. But this doesn't resolve the issue I'm facing with libnames defined in jobs - libnames I don't want to change because then I would have to modify code after unit testing.

Re: Automated Replacement of View with Table

ChrisNZ — Wed, 27 Jan 2021 07:29:14 GMT

> doesn't address the issue with libnames in code

libnames in production code are best avoided I reckon.

That's not always the best strategy, so they could be standardised and contain variables such as

&env (values PRD or DEV) and &project (folder/tree path)

and look like

libname CCFA "&svrdrv\PRD\&project"; in PROD

and be customised to overwrite the PROD library in DEV:

libname CCFAPRD "&svrdrv\PRD\&project" access=readonly; in PROD

libname CCFA ("&svrdrv\DEV\&project" CCFAPRD ); in DEV

To go back to the original question, there is no automatic way that I know to remove views when homonymous data sets are created.

Re: Automated Replacement of View with Table

Patrick — Sun, 21 Feb 2021 05:49:51 GMT

Apologies to only respond now.

Creating concatenated libraries allows me to unit test my code without having to create all the views in my development area. For regression testing I still need the view due to some process I don't touch creating additional libraries which need the views to exist (as they expect the data to be "replicated" in my development area).

So not really a 100% solution but something I can work with practically. Shame that there is no way to instruct SAS to deal with views as if they were tables when it comes to replacing a target table/view.