topic Re: psql functionality in SAS Procedures

psql functionality

cjdw — Sat, 02 May 2015 08:12:55 GMT

I would like to gain some insight into the possibilities available to me.

For the past while I've had the opportunity to get better acquainted with psql. However, I find myself now in an environment where SAS and proc sql are the order of the day. I been playing around with the first. and last. constructs in order to obtain window functionality that resembles the over () construct in psql. I've come accross this site

SAS(R) 9.4 Intelligence Platform: Middle-Tier Administration Guide, Second Edition

In which it looks like I might be able to still use pgadmin (and psql) instead of proc sql.

Is there anybody out there that will be able to give me some pointers on this matter either by referring to

1) How I can still setup pgadmin

2) How I can better get to grips with sas to be able to get (something like) window functions working?

Thanks in advance for any help / advise.

Re: psql functionality

Tom — Sat, 02 May 2015 12:18:41 GMT

The solution probably depends on what your actual problems are.

Perhaps you just just want to call some SAS procedures and use BY group processing or use CLASS variables.

For example look at PROC SUMMARY.

For some simple things PROC SQL will automatically re-merge the detail records with the aggregate function results to eliminate the need for OVER() syntax.

If you have SAS/Access then perhaps you can push your PSQL code into the database.

Re: psql functionality

Reeza — Sat, 02 May 2015 17:14:40 GMT

Look at Pass Through SQL as an option to push the P-SQL code to your DB.

Re: psql functionality

jakarman — Sat, 02 May 2015 17:46:00 GMT

What you have found is the midtier event manager setup/configuration/usage. It is about monitoring reporting the SAS environment in the Enterpise Intelligence approach by IT staff people.

That one is not meant to be used by SAS programmers building SAS applications for the business.

For programming in SAS is doesn't make any sense to want to stick on your old postgres approach. The reason is SAS SQL is ANsI-1999 based and postgres is not using the same kind of dialect.

The over() in SAS is missing as those functions are the ones supported by the SAS procs. There are no window functions with SAS-sql because of that.
Only when the sas/access to postgres is licensed and installed you can use the special postgres interfaces SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

For some databases (not Progres) it is possible to have an "in database" processing option activated, it will propagate a window function to the external DBMS and return the results to those SAS procs.

Base SAS(R) 9.4 Procedures Guide, Third Edition (see the list of DBMS systems).