topic Re: USING LIBNAME in a VIEW in SAS Programming

USING LIBNAME in a VIEW

samlac — Thu, 07 Dec 2023 16:26:33 GMT

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

Re: USING LIBNAME in a VIEW

Sajid01 — Thu, 07 Dec 2023 16:41:52 GMT

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;

Re: USING LIBNAME in a VIEW

samlac — Thu, 07 Dec 2023 16:43:16 GMT

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.

Re: USING LIBNAME in a VIEW

yabwon — Thu, 07 Dec 2023 16:46:41 GMT

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

Re: USING LIBNAME in a VIEW

Sajid01 — Thu, 07 Dec 2023 16:49:34 GMT

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.

Re: USING LIBNAME in a VIEW

Tom — Thu, 07 Dec 2023 17:01:03 GMT

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;

Re: USING LIBNAME in a VIEW

samlac — Thu, 07 Dec 2023 16:55:07 GMT

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.

Re: USING LIBNAME in a VIEW

yabwon — Thu, 07 Dec 2023 16:59:21 GMT

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

Re: USING LIBNAME in a VIEW

Tom — Thu, 07 Dec 2023 17:10:02 GMT

If you want to do the SQL view then the libref you use in the USING clause does not have look anything like the actual libref that exists. The important part is that the PATH used there is the one you want them to notice.

proc sql;
create view "&outpath/class_sqlv" as
  select * 
  from x.class
  where sex='M'
  using libname x "&inpath"
;
quit;

proc print data="&outpath/class_sqlv";
  title "'&outpath/class_sqlv'";
run;

Re: USING LIBNAME in a VIEW

samlac — Thu, 07 Dec 2023 18:05:28 GMT

Hello,

I tried this:

%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;

this actually works great.

If I use my own tables and my own libname then it only work during the same session. If I close my current SAS session and try to open the view i created previously I get this error: Cannot open data set "central". failure loading ECI00001.Central.VIEW. Error detected during View Load request.

Re: USING LIBNAME in a VIEW

yabwon — Thu, 07 Dec 2023 18:10:27 GMT

Which version of sas do you use?

On my M8 I have:

1    %let path=R:\;
2    libname dummy "&path" ;
NOTE: Libref DUMMY was successfully assigned as follows:
      Engine:        V9
      Physical Name: R:\
3    data test;
4    set dummy.class_v;
5    run;

NOTE: View DUMMY.CLASS_V.VIEW used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

NOTE: There were 19 observations read from the data set R:\/class.
NOTE: There were 10 observations read from the data set DUMMY.CLASS_V.
NOTE: The data set WORK.TEST has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds

bart

Re: USING LIBNAME in a VIEW

Sajid01 — Thu, 07 Dec 2023 18:13:54 GMT

Using libnames it should work as long as view's are supported. M8 is not necessary.

Re: USING LIBNAME in a VIEW

yabwon — Thu, 07 Dec 2023 18:15:51 GMT

btw. how's SQL version?

In your session:

libname dummy "C:\test" ;
data dummy.class ; 
set sashelp.class; 
run;


libname mySesion "C:\downloads";

PROC SQL;
        CREATE VIEW mySesion.Central AS
           SELECT   *
            FROM x.class
            where sex='M'

        USING LIBNAME x "C:\test";
        ;
QUIT;

in bran new one:

libname newSas "R:\downloads";
proc print data=newSas.central;
run;

bart

Re: USING LIBNAME in a VIEW

Kurt_Bremser — Thu, 07 Dec 2023 18:17:09 GMT

First of all,

.sas7dbat

is not a valid extension for any SAS file. The valid extension for a SAS dataset file is

.sas7bdat

Next, SAS dataset filenames must be all lowercase, particularly on the case-sensitive UNIX system you apparently use.

Second, SAS SQL does not have a USING clause.

Third, you can't do anything with SAS datasets without running SAS code. What you can do is to make it easier for users to run the code, e.g. by storing it in an easily accessible .sas program file. Or, if you have a BI Server, you can define stored processes which are easily run from a web interface. It depends on the way your users run the SAS system (locally installed, or client/server with either SAS Studio or Enterprise Guide).

Re: USING LIBNAME in a VIEW

Kurt_Bremser — Thu, 07 Dec 2023 18:19:37 GMT

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

Re: USING LIBNAME in a VIEW

yabwon — Thu, 07 Dec 2023 18:19:37 GMT

"Second, SAS SQL does not have a USING clause." - it does.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0nolnbokay91in1gouzgw3xzl5e.htm

Bart

Re: USING LIBNAME in a VIEW

samlac — Thu, 07 Dec 2023 18:20:20 GMT

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.

Re: USING LIBNAME in a VIEW

yabwon — Thu, 07 Dec 2023 18:21:34 GMT

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

But here I didn't know variables in datasets.

Re: USING LIBNAME in a VIEW

Kurt_Bremser — Thu, 07 Dec 2023 18:22:35 GMT

I stand corrected. Funny that SAS did not document it as a clause in the list of SQL elements (where I looked).

Re: USING LIBNAME in a VIEW

Kurt_Bremser — Thu, 07 Dec 2023 18:24:21 GMT

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.