topic Re: creating view in proc sql without the creation in work of the corresponding data set in SAS Procedures

creating view in proc sql without the creation in work of the corresponding data set

valleet — Wed, 11 Jan 2017 17:44:58 GMT

Dear all,

I am using Enterprise 5.1.

I am trying to use "proc sql" and "create view" in a macro to create 100 views that I want to use later in my program one by one.

But, althouth It is written everywhere that the advantage of views is that they do no contain data and execute only when

called, my program actually creates the 100 files when I call this code:

%do %while ....

proc sql;

create view as dsNb&nb as select .....;

quit;

If I use the noexec option, then when I call the view later on, I do not get back the correct data set but the last data set without

the noexec.

Do someone knonw how to prevent SAS to create the data sets corresponding to the views as I run my macro ?

Thanks in advance for any help,

Thierry

Re: creating view in proc sql without the creation in work of the corresponding data set

Astounding — Wed, 11 Jan 2017 18:13:35 GMT

Have you tried changing "view as" to "view" (removing the first "as")?

Re: creating view in proc sql without the creation in work of the corresponding data set

mnjtrana — Thu, 12 Jan 2017 06:26:29 GMT

You can write the sas view definitions to physical text files and then include the same in your code when required.

Below is one such example, you need to trim it as per your scenario.

filename sasview "c:\users\cxx\desktop\views.txt";

DATA _NULL_;
	set viewdef end=last;
	i=1;
	do while(i=100);
		i+1;
		file sasview mod;
		put 'proc sql;';
		put 'create view ' viewname&nb 'as select';
		put 'from ' col_name;
                put ' group by' grp_naame ';';
		put 'order by' orderby_stat;
                put ';';
		put 'quit;';
	end;
run;

%inc sasview;

Re: creating view in proc sql without the creation in work of the corresponding data set

DanielSantos — Thu, 12 Jan 2017 10:01:12 GMT

Hi.

There should be no data inside those files (*.sas7vew) , only the definition of the view.

Should have just a few KB in size each.

If you have created the view with PROC SQL, you can issue the following command to check the view's definition

proc sql;

describe view libname.viewname;

quit;

Still if the those files are a problem check@mnjtrana solution to create them only at the time you need them.

Hope it helps.

Daniel Santos @ www.cgd.pt

Re: creating view in proc sql without the creation in work of the corresponding data set

valleet — Thu, 12 Jan 2017 10:11:21 GMT

Thhanks Astounding for your answer.

SAS definitively need the "as" there.

But now I think that what is stored in WORK is actually the view as the icone used is slightly different from the one use for data sets (although the Properties menu

indicates the object type as "data set".

So it seems so that my real problem is that the data sets corresponding to the views are output as data sets in the output panel, or whatever it is called, How could I avoid that.

Thierry

Re: creating view in proc sql without the creation in work of the corresponding data set

valleet — Thu, 12 Jan 2017 10:19:21 GMT

Thanks Daniel for your answer.

Now I think that what is stored in WORK is actually the views as the icone used is slightly different from the one use for data sets (although the Properties menu

indicates the object type as "data set").

So it seems so that my real problem is that the data sets corresponding to the views are output as in the "output panel", or whatever it is called, Is it possible to avoid that.

Thierry

Re: creating view in proc sql without the creation in work of the corresponding data set

LinusH — Fri, 13 Jan 2017 07:12:33 GMT

I'm not really following, what is the actual problem?
When you define views corresponding SAS files are created but they contain no data, just the definition ("meta data"). Can't see any problem hee since those very light weight. The problem will problem will more likely to occur when calling your views, it's not very efficient to call the same table that many times, but that will show later...