DATA Step, Macro, Functions and more

proc sql create view with dynamic sas.userid macro var

Accepted Solution Solved
Reply
Regular Contributor
Posts: 207
Accepted Solution

proc sql create view with dynamic sas.userid macro var

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(%') ))


Accepted Solutions
Solution
‎09-19-2011 08:53 AM
Frequent Contributor
Frequent Contributor
Posts: 94

proc sql create view with dynamic sas.userid macro var

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?

View solution in original post


All Replies
Trusted Advisor
Posts: 1,301

proc sql create view with dynamic sas.userid macro var

You could do this before the proc sql step:

data _null_;

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

run;

then change saslogin=&usr in proc sql.

Regular Contributor
Posts: 207

proc sql create view with dynamic sas.userid macro var

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' )) );
*/

Trusted Advisor
Posts: 1,301

proc sql create view with dynamic sas.userid macro var

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

Regular Contributor
Posts: 207

proc sql create view with dynamic sas.userid macro var

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 */

Trusted Advisor
Posts: 1,301

proc sql create view with dynamic sas.userid macro var

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.

Frequent Contributor
Frequent Contributor
Posts: 94

proc sql create view with dynamic sas.userid macro var

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.

Regular Contributor
Posts: 207

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

@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.

Regular Contributor
Posts: 207

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

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

Frequent Contributor
Frequent Contributor
Posts: 94

proc sql create view with dynamic sas.userid macro var

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!

Super User
Super User
Posts: 7,079

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

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;

Frequent Contributor
Frequent Contributor
Posts: 94

proc sql create view with dynamic sas.userid macro var

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.

Regular Contributor
Posts: 207

proc sql create view with dynamic sas.userid macro var

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

Frequent Contributor
Frequent Contributor
Posts: 94

proc sql create view with dynamic sas.userid macro var

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.

Super User
Super User
Posts: 7,079

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

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')

  ;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 804 views
  • 7 likes
  • 4 in conversation