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;
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.
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
Using libnames it should work as long as view's are supported. M8 is not necessary.
Which just means if you code GLOBAL statements in the middle of the data step that is defining the VIEW they are NOT part of the view. Which makes sense. You probably shouldn't put GLOBAL statements into the middle of a step anyway as it will just confuse you about the actual order of operation.
Note there is not need to embed a LIBNAME definition in the middle of a data step view. That is a feature they added to PROC SQL. Which means that in data step views you can only "embed" access to SAS datasets or views.
If you did need to embed a link to an external database you would need to make an SQL view.
If you also need data step functionality then make two views and have the data step view reference the SQL view.
libname target "&path";
proc sql;
create view target.oracle_view as
select * from x.table1
using libname x oracle .... ;
;
quit;
data target.ds_view / view=target.ds_view;
set "&path/oracle_view.sas7bvew" ;
run;
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
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).
"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
I stand corrected. Funny that SAS did not document it as a clause in the list of SQL elements (where I looked).
You are right, I would make it more "visible" too. It took me a bit to find it a few years ago when I needed this kind of feature.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.