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 ... View more

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

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 */ ... View more

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