topic Re: relatvive reference when using VIEWS in SAS Programming

relatvive reference when using VIEWS

samlac — Fri, 14 Jun 2024 15:39:42 GMT

Hello,

I'm having an issue with this code:

%IF &SAVE_OUTPUT. = Y %THEN %DO;
PROC SQL _METHOD UNDO_POLICY=NONE;
CREATE VIEW &TABLES_SAVE_PATH_LIBNAME..PnC_Quote_Central AS
SELECT MAT.*,
MAIN.*
FROM &TABLES_SAVE_PATH_LIBNAME..SUBMISSION_MATURITY_TABLE (WHERE=(MATURITY_GROUP='Ultimate')) MAT
LEFT JOIN &TABLES_SAVE_PATH_LIBNAME..SUBMISSION_MAIN_TABLE MAIN
ON MAT.AGREEMENT_NUMBER=MAIN.AGREEMENT_NUMBER
USING LIBNAME &TABLES_SAVE_PATH_LIBNAME. "&TABLES_SAVE_PATH.";
QUIT;
%END;

Although its working great, if I move the folder that contain the 2 referenced tables and my view to some other location. The view will not be working as the libname was a static path.

How can I make the reference to my two tables relative to the path of the location of the view I want to create? So that I can move my folder anywhere without breaking the View.

Best regards,

Samuel

Re: relatvive reference when using VIEWS

Kurt_Bremser — Fri, 14 Jun 2024 15:54:08 GMT

You need to redefine the library to the new physical path. In SQL, there is no „relative“ path to a table. A single-level name will always point to WORK or USER.

Re: relatvive reference when using VIEWS

samlac — Fri, 14 Jun 2024 16:45:10 GMT

Thank you for the quick reply. By curiosity, would there be a way to do it with a Dataset? By your answer, I understand that the limitation comes from SQL.

Re: relatvive reference when using VIEWS

Kurt_Bremser — Fri, 14 Jun 2024 20:09:39 GMT

@samlac wrote:
Thank you for the quick reply. By curiosity, would there be a way to do it with a Dataset? By your answer, I understand that the limitation comes from SQL.

No. The physical location of a dataset is determined through the library assignment, no matter where in SAS you use them.