SQL Create Table using variable names from SAS Catalog

[david27]

Hello,

I have this code and it works fine:

data variable_names;
	input var_names $32.;
	datalines;
name
age
sex
height
weight
;
run;

filename varname catalog "work.catalog.mycode1.source";
filename abbrev catalog "work.catalog";

data adfadsf;
	file varname;
	set variable_names end=eof;
	_var_names=cats(',',var_names);

	if _n_ =1 then
		do;
			put "select";
			put var_names;
		end;
	else put _var_names;
run;

proc sql;
		%include varname;
	from sashelp.class
	;
quit;

Please notice ABOVE: I am NOT creating a table.

But if I try to create a Table and the code errors out.

proc sql;
	create table t2 as
		%include varname;
	from sashelp.class
	;
quit;

Please help.

Thanks

[PaigeMiller]

We need to know what is contained in

work.catalog.mycode1.source

as clearly, the usage of this code isn't working if you are using SQL to create a table. 

[david27]

and How can I find that?

[PaigeMiller]

---

@david27 wrote:

and How can I find that?

---

Don't you know what is in that catalog entry you call varname? If not, why are you trying to use it?

Anyway, it appears that %include cannot be used inside the CREATE TABLE clause.

[david27]

Sir,

in that catalog entry this is what is supposed to be:

select

name

,age

,sex

,height

,weight

Why I say that:

Because the query works when i am not doing a "create table.."

I am learning how to use catalog and came across this issue. So reaching out to more knowledgable folks.

[PaigeMiller]

It appears that %include cannot be used inside the CREATE TABLE clause.

Which brings up the question ... why store part of your code in this catalog entry, and then try to insert it into other code, why not just have it all in one program without the need for a catalog entry?

[david27]

The variable names are macro driven.

It is possible the variable names will exceed the length of 65K in a very near future for me.

So instead of doing a select into:..... then creating a macro and doing &variablenames.

I adopted the catalog approach.

[Kayalvizhi]

instead of %include varname, try with var_name, because varname is a keyword in sas

[david27]

It does not seem to work.

I changed it to "myname"

data variable_names;
	input var_names $32.;
	datalines;
name
age
sex
height
weight
;
run;

filename myname catalog "work.catalog.mycode1.source";
filename abbrev catalog "work.catalog";

data adfadsf;
	file myname;
	set variable_names end=eof;
	_var_names=cats(',',var_names);

	if _n_ =1 then
		do;
			put "select";
			put var_names;
		end;
	else put _var_names;
run;

proc sql;
	create table t2 as
		%include myname;
	from sashelp.class
	;
quit;

[Tom · Accepted solution]

It is really not a good idea to try to use %INCLUDE to include PART of a statement from a text file. (A source catalog is just another way to store a text file)

It can work if you use it to include the BEGINNING of a statement by not including a trailing semi-colon in the text file, but not when you want to stick it into the middle of a statement.

So either put the beginning of the statement into the code file. 

Or make a new code file and put that into it and use the new file.

filename create temp;
data _null_;
  file create ;
  if _n_=1 then put 'create table t2 as' ;
  infile varname;
  input ;
  put _infile_;
run;

proc sql;
%include create/source2;
from sashelp.class
;
quit;

Another trick would be a include a reference to a macro variable at the top of your include file.  Then change the value of the macro variable.  So your include file might look like:

&headercode.
select 
 name
,age
,sex

And you two programs are then:

proc sql ;
%let headercode=;
%include varnames;
from sashelp.class;

%let headercode=create table t2 as ;
%include varnames;
from sashelp.class;
quit;