|
Hi, Kevin, try to run the following program. I think it solved your problem.
data xx;
array score(10);
do i=1 to 10;
do j=1 to dim(score);
score(j)=ranuni(0);
end;
output;
end;
drop i j;
run;
ods output 'Variables'=xxColumns;
proc contents data=xx;
run;
proc sql;
create table remain_columns as
select variable from xxColumns
where variable not in ('score2', 'score4', 'score6');
quit;
proc transpose data=remain_columns out=temp(drop=_name_);
id variable;
run;
proc sql;
create view xxView as
select * from temp
union corr
select * from xx;
quit;
proc print data=xxView;
run;
ods output close;
Best,
wenge
wenge.guo@ndsu.nodak.edu
Kevin Myers wrote:Hi folks,I'd like to create a SQL view in SAS that
excludes specific columns from one or more of the input tables. So, I tried
the following:proc sql; create view xRegDistrictView as select * from
regDistrict (drop=entCount) natural join xStateView;quit; The view gets
created fine, but when I actually try to use this view in any manner
running SAS 8.2 under Windows 2K, the view dies a miserable death with a
read access violation. For example, attempting to open the view in a
viewtable window produces the following result:ERROR: Read Access Violation
In Task ( ViewTable Window ]Exception occurred at (62451BBC)Task
TracebackERROR: Generic critical error.It was easy to narrow the source of
this problem down to my use of the drop= data set option on one of the
source tables for the view. Note that using drop= in this manner works fine
if you create a TABLE, but apparantly SAS doesn't like this for some reason
when you create a VIEW instead.So, my problem now is how to create a SAS
view that EXCLUDES specific columns from an individual source table. The
desired solution isn't as simple as merely listing out all of the columns
that I want to include, as there are a LOT of columns for many different
tables involved, and listing all of the desired columns would be very
tedious, time consuming, error prone, and would produce undesirable code
maintenance headaches. I've considered using a macro to generate an
appropriate list of columns to include, however that is also a more complex
and less desirable solution. Two questions:1. Does anyone know if/when the
problem described above has been solved in SAS (e.g. V9), or if it ever
will be?2. Does anyone know of a simple technique that can be used to
create a SQL view that excludes a specific set of columns from individual
source tables? As mentioned previously, note that listing all non-excluded
columns or using a macro to generate such lists are not desirable
solutions. Also note that using a data step view in this context is also
unacceptable from a performance standpoint, because that wouldn't allow
subsetting critieria that may be applied to the view to be passed through
to the underlying tables.Thanks in advance for any
tips/tricks/ideas/insight.s/KAM
|
