USING LIBNAME in a VIEW

[samlac]

Hello,

I've been trying to generate a View with either DATA SET or PROC SQL that would be accessible by anyone in my team afterward whitout the need to run SAS code. In other word, I would expect that everything is included in the VIEW to be able to view it without requiring running additional SAS code to get LIBNAME definition.

With DATA SET I tried to bypass the use of LIBNAME and put the path directly, but I get an error.

DATA "/root/balbalba/reter/Central.sas7dbat" / VIEW="/root/balbalba/reter/Central.sas7dbat";
        SET "/root/balbalba/reter/Other_Table.sas7dbat";

        SET "/root/balbalba/reter/Other_Table2.sas7dbat" KEY = AGREEMENT_NUMBER / UNIQUE;
        IF _IORC_=%SYSRC(_DSENOM) THEN DO;
            _ERROR_ = 0;
        END;
    RUN;

This give me an Error on the 

VIEW="/root/balbalba/reter/Central.sas7dbat" 

• ERROR 22-322: Expecting a name.
• ERROR 200-322: The symbol is no recognized and will be ignored.

I also tried to use the functionalty 'USING LIBNAME' in a PROC SQL:

Notes:
&TABLES_SAVE_PATH_LIBNAME. = REP_DB (which is a LIBNAME defined in previous code)
&TABLES_SAVE_PATH. = "/root/balbalba/reter"

PROC SQL;
        CREATE VIEW &TABLES_SAVE_PATH_LIBNAME..Central AS
           SELECT   MAT.*,
                    MAIN.*
            FROM &TABLES_SAVE_PATH_LIBNAME..Other_Table MAT
            USING LIBNAME &TABLES_SAVE_PATH_LIBNAME. &TABLES_SAVE_PATH.
            LEFT JOIN &TABLES_SAVE_PATH_LIBNAME..Other_Table2 MAIN
                ON MAT.AGREEMENT_NUMBER=MAIN.AGREEMENT_NUMBER;
    QUIT;

Which give me these error:

• ERROR: Library REP_DB does not exist.
  ERROR: SQL view was not defined due to errors.

Nothing seem to work... If anyone could give me a solution that would be really nice.

Best regards,

Samuel

[Sajid01]

Please try this modified version of the code

Libname lib1 "/root/balbalba/reter";

DATA lib1.Central / VIEW= lib1.Central_v;
        SET lib1.Other_Table;
        SET lib1.Other_Table2 KEY = AGREEMENT_NUMBER / UNIQUE;
        IF _IORC_=%SYSRC(_DSENOM) THEN DO;
            _ERROR_ = 0;
        END;
    RUN;

[samlac]

Hello,

I already thied that, but it does work at creating the VIEW, but the VIEW wont work after as the lib1 wont be defined anymore. I need the LIBNAME to be part of the VIEW itself.

[Sajid01]

Libname is a reference to path .
Therefore when wants to use the view the reference to that location or libname will have to be mentioned again.

[yabwon]

Hi,

SQL is an animal which likes statements in certain order, I would try with:

PROC SQL;
        CREATE VIEW &TABLES_SAVE_PATH_LIBNAME..Central AS
           SELECT   MAT.*,
                    MAIN.*
            FROM &TABLES_SAVE_PATH_LIBNAME..Other_Table MAT
            LEFT JOIN &TABLES_SAVE_PATH_LIBNAME..Other_Table2 MAIN
                ON MAT.AGREEMENT_NUMBER=MAIN.AGREEMENT_NUMBER

        USING LIBNAME &TABLES_SAVE_PATH_LIBNAME. &TABLES_SAVE_PATH.
        ;


    QUIT;

BTW. macrovariables are resolved during view generation and stored view will have constant values (for libname and path)

Bart

[samlac]

Hello,

Just gave it a shoot and i'm still gettin gthe same error: Library REP_DB does not exist.

regarding your comment "BTW. macrovariables are resolved during view generation and stored view will have constant values (for libname and path)" wouldnt that mean that just using the LIBNAME would stored its definition? because it is definetly not doing so right now. if I use normal DATA SET with the reference to the LIBNAME the view create properly and will work fine untill i clear my session and dont ahve the LIBNAME defined anymore in my session. So its mena that the definition of the LIBNAME was not Saved in the view and only the name that refer to the path.

[yabwon · Accepted solution]

libname mySesion "/path/where/view/will/be/stored";

PROC SQL;
        CREATE VIEW mySesion.Central AS
           SELECT   MAT.*,
                    MAIN.*
            FROM source.Other_Table MAT
            LEFT JOIN source.Other_Table2 MAIN
                ON MAT.AGREEMENT_NUMBER=MAIN.AGREEMENT_NUMBER

        USING LIBNAME source "/path/where/source/tables/are/stored";
        ;


    QUIT;

try like this.

Bart

[Kurt_Bremser]

Please note that your code will draw a WARNING for a variable already included. Never use asterisks in joins like this.

[yabwon]

Agree 100%, for production jobs only explicit lists of variables.

But here I didn't know variables in datasets.

[Kurt_Bremser]

The ON clause reveals that at least the key variable is common in both datasets. That's why one can be sure of the WARNING.

[yabwon]

But of course! I should spot that myself! 😄

[samlac]

The first time I tried this one. I forgot to put the double quote for the path, so that didnt work. I tried again with double quote and its working good.

for reference:

libname mySesion "/path/where/view/will/be/stored";

PROC SQL;
        CREATE VIEW mySesion.Central AS
           SELECT   MAT.*,
                    MAIN.*
            FROM source.Other_Table MAT
            LEFT JOIN source.Other_Table2 MAIN
                ON MAT.AGREEMENT_NUMBER=MAIN.AGREEMENT_NUMBER

        USING LIBNAME source "/path/where/source/tables/are/stored";
        ;


    QUIT;

However, havent found a way to make it work with DATA SET. They just dont save the LIBNAME in the view.. which is aligned with their documentation. There is only documentation about embedded LIBNAME for PROC SQL. I find it funny that the native SAS DATA SET doesnt support it, but the SQL does.

Thanks for your help.

[Tom]

However, havent found a way to make it work with DATA SET. 

I showed you how to do it with a data step.  Just use the quoted physical path for any datasets you reference in the body of the data step.

[yabwon]

Data steps don't allow to "embed" libname definition inside their views. Only SQL allows this. 

BTW for datasets I personally consider 

set "/direct/path/to/a/data/set/namedXYS.sas7bdat";

bad programming practice.

Bart

[Tom]

Your idea of using a path in the SET statement should work.

But you don't need to do that in the DATA statement.   Just make a libref and use normal two level names when making the file. Once the file exists you can use quoted physical name to reference the file.  Remember that views use sas7bvew as the extension.

Try this example:

%let path=c:\downloads;
libname dummy "&path" ;
data dummy.class ; set sashelp.class; run;

data dummy.class_v/ view=dummy.class_v;
  set "&path/class" ;
  if sex='M';
run;

proc print data=dummy.class_v;
  title 'dummy.class_v';
run;

proc print data="&path/class_v" ;
  title "'&path/class_v'";
run;

proc print data="&path/class_v.sas7bvew" ;
  title "'&path/class_v.sas7bvew'";
run;
title;