topic proc sql create view with dynamic sas.userid macro var in SAS Programming

proc sql create view with dynamic sas.userid macro var

metalray — Thu, 15 Sep 2011 14:10:04 GMT

Hello Guys,

we try to create a proc sql view that fetches data for each user individually,

Unfortunately, if we use %str(%')&sysuserid.%str(%')

the user ID of the creator of this view gets replaced and is fixed.
How would we create a view that is creating a dynamic WHERE clause?

proc sql ;
connect to oracle as oradb (user='sas' password='{xxxxx
path=xxxadas);

create view mysas.ph_filtered_facts as
select *
from connection to oradb
(
SELECT FAKT FROM DEV.FAK
WHERE Wer IN
(SELECT Wer FROM DEV.vk WHERE SK = 1 AND
VK IN
(SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = %str(%')&sysuserid.%str(%') ))
);

run;
disconnect from oradb;
quit;
run;
proc print data=pt_example;
title "Using SQL Pass-Through";
run;

We also tried:

%str(%')symget(_metauser)%str(%') ))

proc sql create view with dynamic sas.userid macro var

FriedEgg — Thu, 15 Sep 2011 14:43:47 GMT

You could do this before the proc sql step:

data _null_;

call symput('usr',"'"||"&sysuserid"||"'");

run;

then change saslogin=&usr in proc sql.

proc sql create view with dynamic sas.userid macro var

metalray — Thu, 15 Sep 2011 15:28:42 GMT

Hi FriedEgg,

a good try but the view is still not dynamic. The describe statement still contains the creators user ID, even when executing the describe with other users

data _null_;
call symput('usr',"'"||"&sysuserid"||"'");
run;

proc sql ;
connect to oracle as oradb (user='....' password='.....'
path='.....');
create view mysas.ph_filtered_facts as
select *
from connection to oradb
(
SELECT FAKT FROM DEV.FAKTEN
WHERE Wer IN
(SELECT Wer FROM DEV.vk WHERE SK = 1 AND
VK IN
(SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = &usr ))
);
run;
disconnect from oradb;
quit;
run;
/*also tried: (SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = %str(%')&sysuserid.%str(%') ))*/

/*
proc sql;
   describe view mysas.ph_filtered_facts
/*Result*/
        select *
          from connection to oradb
                  /* dbms=oracle, connect options=(user='.....' password=XXXXX path='.....') */
                  ( SELECT FAKT FROM DEV.FAKTEN WHERE Wer IN (SELECT Wer FROM DEV.vk WHERE SK = 1 AND
VK IN (SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = 'S39' )) );
*/

proc sql create view with dynamic sas.userid macro var

FriedEgg — Thu, 15 Sep 2011 15:37:49 GMT

How are you running the script as different user? Did you check that &sysuserid is showing the expected user appropriatly during your runs?

proc sql create view with dynamic sas.userid macro var

metalray — Thu, 15 Sep 2011 15:49:36 GMT

I run it in EG, changing the connection profile.
I also use the view in an information map,changing connection profiles, still one and the same result.

%put &sysuserid; /* shows user id */

With the describe statement, I check how the view is defined for each user,
besides that, I also know the data, and that must be different for each user
but it is not, always shows S39.

proc sql;
describe view mysas.ph_filtered_facts;
/* still shows the user of the creator of the view,S39 , hence wrong data when I use user SDW */

proc sql create view with dynamic sas.userid macro var

FriedEgg — Thu, 15 Sep 2011 16:01:17 GMT

What authentication mode are you using to spawn the conncetions, does each profile use the same user? &sysuserid is the system user, so it should be equal to the value of the user that performs the login to that session.

proc sql create view with dynamic sas.userid macro var

DF — Thu, 15 Sep 2011 16:29:09 GMT

Is it necessary to use SQL in pass-thru mode? If you were able to write it directly against the oracle database (i.e. it'd have to be linked using libname) then you could use SYMGET() in your view definition instead.

Re: proc sql create view with dynamic sas.userid macro var

metalray — Fri, 16 Sep 2011 07:01:00 GMT

@FriedEgg,What do you mean?
The "connect to oracle as oradb (user='....' password='.....' path='.....'); is always the same users" is obviously always
the same users, its the schema that contains the oracle tables that I use in the SQL statement.

I am a bit worried that there is no straight answer to that. Has none thought of using SQL PassThrough with the current user ID before?
FriedEgg, I am aware what the sysuserid is and I see that the %put &sysuserid; shows me the correct one.

If I execute as S39 it shows S39, if I execute as SDW is shows SDW but the PROC SQL itself, always shows S39, the creator.

To me, the problem I have looks almost logical considering the logic that the SAS team applies when developing the great software for us.
When I execute the CREATE VIEW and substitute the sysuserid in the WHERE clause then that is nicely done during creation time
but that does not mean its a "dynamic" view.

@DF, good idea. I have an oracle (SAS ACCESS) library. but I am very reluctant to use a DATA step.

creating a SAS data set is not viable. one of the three tables contains a million rows and I

cant do that for every user. - hence the usage of a VIEW.

Re: proc sql create view with dynamic sas.userid macro var

metalray — Fri, 16 Sep 2011 10:24:37 GMT

using a data step, I wonder how the nesting or join would work.

I cant loop through the millions of rows.

/*Nesting: http://www.repole.com/dinosaur/fuzzymerge.html*/
data mysas.PH_TEST_VIEW (keep=VK);
set dev.FAKTEN;
where WER IN ('S39');
run;

1. DEV.SASLOGINS about 1000 rows
user login to vk groups

2. DEV.vk about 1000 rows
vk groups to individual Wer

3. DEV.FAK millions of rows
individual Wer to all their data

proc sql create view with dynamic sas.userid macro var

DF — Fri, 16 Sep 2011 10:56:51 GMT

Assuming your db is linked in SAS as "Dev", I was thinking something like:

proc sql;

create view mysas.ph_filtered_facts as

select

fakt

from dev.fak

inner join dev.vk on fak.wer = vk.wer and vk.sk=1

inner join dev.saslogins on saslogins.vk = vk.vk

where

saslogins.login = symget('sysuserid');

quit;

Obviously I can't test it, and I might have confused your table linkage - I was trying to remove the subqueries and I think SAS wouldn't deal with these very well!

Re: proc sql create view with dynamic sas.userid macro var

Tom — Fri, 16 Sep 2011 11:28:00 GMT

It looks to me like you are trying to reference a SAS macro variable in the code that is running in the database. I do not think that is possible.

You might want to instead look for an ORACLE (or whatever your database is) function that can return the ORACLE userid. But that might not match the userid values that are stored in your database.

If you want to do this on just the SAS side then you could use the SYMGET function in your view. Try this program in SAS.

proc sql noprint ;

create view test1 as

select * from sashelp.class

where name = symget('name')

;

quit;

%let name=Mary ;

data _null_;

set test1;

put name=;

run;

%let name=Philip;

data _null_;

set test1;

put name=;

run;

proc sql create view with dynamic sas.userid macro var

DF — Fri, 16 Sep 2011 11:34:57 GMT

That's a good idea. The below works on my Oracle setup:

SELECT SYS_CONTEXT('USERENV','OS_USER') os_user from DUAL

You could just plug that into the original SQL view and not bother with the macro variable, assuming it returns the correct user in your setup.

proc sql create view with dynamic sas.userid macro var

metalray — Fri, 16 Sep 2011 11:53:42 GMT

Hello Guys,

Many thanks for your suggestions. I think I never got so many responses here.
First, yes, the Oracle User ID is very different from the user ID of the
view user. There is and will never be a match (@DF). We
have a thousand users for SAS but not a thousand Oracle USERIDs.

@Tom, you pointed it out rightly, I cant use a SAS macro variable in the code that is
running on the database, hence my describe view statement always showed the
sasuserid of the creator of the view. I was hopeing this can be substituted at
runtime but obviously not.

Now, we try to do a SAS DATA VIEW to allow putting the sysuserid of the individual executer
in the view and create a truly user-dependent-dynamic-view of data.

The data is quite large and I wonder how to efficiently link it using
a SAS DATA VIEW. I cant merge the tables, it will create millions of rows.

Regards,

Bob

proc sql create view with dynamic sas.userid macro var

DF — Fri, 16 Sep 2011 12:22:31 GMT

Hi Bob,

The Oracle code I posted above returns the local OS username, rather than the oracle login. In my case, even though I've logged into oracle as the user "SAS", it returns "DF" because that's the login I've used on the system sas is running on.

I've tested it here using several logins and even though my code uses the same oracle username/password, the query returns the OS user I'm logged in as.

HTH.

Re: proc sql create view with dynamic sas.userid macro var

Tom — Fri, 16 Sep 2011 12:32:26 GMT

You could see if SAS is smart enough to push the WHERE clause from the SAS side of the query into the database.

create view mysas.ph_filtered_facts as

select *

from connection to oradb

(select A.FAKT,C.SASLOGIN

from DEV.FAKTEN A

, DEV.VK B

, DEV.SASLOGINS C

where A.Wer = B.Wer

and B.vk = C.vk

and B.sk = 1

)

where SASLOGIN = symget('sysuserid')

;

proc sql create view with dynamic sas.userid macro var

metalray — Fri, 16 Sep 2011 14:26:36 GMT

@Tom,
good idea but with your SQL you are merging all the tables or
even creating some kind of cross/full join, something I cant do with
a million rows.

@DF,
the idea with the OS User seems to work fine when opening the
View in EG but when using the view in an information map the
data returned is always nothing/empty. Please explain me,
why does OS_User work if the sas user does not exist on the
oracle database as a user?

proc sql create view with dynamic sas.userid macro var

DF — Fri, 16 Sep 2011 14:42:02 GMT

Oracle knows the OS login for the connecting user, even though you use different credentials to log in.

I'm not sure about the informaion map issue - I've only tested the view using EG under a few different accounts, having created it in a shared library.

proc sql create view with dynamic sas.userid macro var

metalray — Mon, 19 Sep 2011 07:39:46 GMT

Hi DF,

the connecting user is always the same as specified in

proc sql ;

connect to oracle as oradb (user='....' password='.....'

path='.....');

How can it be that Oracle knows the SAS executing user? - because

the executing user (sysuserid) is the one that will have to go in the WHERE clause

to only get the records for that specific user)

proc sql create view with dynamic sas.userid macro var

DF — Mon, 19 Sep 2011 12:53:52 GMT

I'm not an Oracle expert, so I can't really answer for certain. I suppose the OS user is just part of the information the Oracle driver passes from your system to the Oracle database - it makes sense that the information would be available as some setups would use this as the authentication method instead of specifying a username/passwords.

So while I log into Oracle as the user "oracle_user", so my oracle username/password gets sent, Oracle also gets told I'm logged into my local operating system as "DF" - and that's what goes into OS_USER. When it's a SAS process that connects to Oracle, then Oracle is told the username that the SAS process is running under.

There's lots of other information available using the same function: http://psoug.org/reference/sys_context.html might be worth having a look at.

Is the pass-through view now working as desired?

proc sql create view with dynamic sas.userid macro var

metalray — Tue, 20 Sep 2011 12:26:42 GMT

Hi DF,

works great. thanks a lot.