Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 6

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

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

 

 

Super User
Posts: 5,085

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

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

 

Contributor
Posts: 52

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

[ Edited ]

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;

Cheers from India!

Manjeet
Super Contributor
Posts: 474

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

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

Occasional Contributor
Posts: 6

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


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







Occasional Contributor
Posts: 6

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


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





Super User
Posts: 5,259

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

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...
Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 160 views
  • 0 likes
  • 5 in conversation