selecting only character variables

[SAS_inquisitive]

Hello:

I am trying to get only character variables using the following program.

%macro m;
%let dsid = %sysfunc(open(sashelp.class));

proc sql noprint;
	create table t1 as
	 select 

	 %do i =  1 %to  5;

	  %if %sysfunc(vartype(&dsid, &i)) = 'C' %then  %sysfunc(varname(&dsid, &i)),;

	  %end;

	 from sashelp.class;
quit;

%mend m;
%m

[Reeza]

Do you have to use SQL? It doesn't support variable lists which is much easier in a data step. 

If you absolutely have to it's probably easier to hit the sashelp.vcolumn table first and get the list of variables and the use that in your query.

data class;
set sashelp.class;
keep _character_;
run;

---

@SAS_inquisitive wrote:

Hello:

I am trying to get only character variables using the following program.

%macro m;
%let dsid = %sysfunc(open(sashelp.class));

proc sql noprint;
	create table t1 as
	 select 

	 %do i =  1 %to  5;

	  %if %sysfunc(vartype(&dsid, &i)) = 'C' %then  %sysfunc(varname(&dsid, &i)),;

	  %end;

	 from sashelp.class;
quit;

%mend m;
%m

---

[JChambo]

You can use dataset options inside proc sql.

data have;
retain
	num1-num3 0
	char1-char3 'a'
	num4-num6 1
	char4-char6 'b';
run;

proc sql;
create table want as
select *
from have (keep=_character_)
;quit;

[SAS_inquisitive]

@Reeza @JChambo, I was trying to see if Variable functions can used to get the list of variables in SELECT statements in PROC SQL.

[emrancaan]

proc sql;
		SELECT name into :Charvlist separated by ' '
        from dictionary.columns
        where memname = upcase("Cars")
		and libname =upcase("SASHElp")
		and type='char' 
;QUIT;


%Put &Charvlist;

[SAS_inquisitive]

@emrancaan I know this way. I was looking for using variable functions as I did post originally.

[Reeza]

1. You did not say that. 

2. You need to account for the last variable which will require no comma, otherwise, this sort of works:

3. You do not put quotation marks around macro comparisons - which is your main issue.

4. Use the debugging options to help you see what is being generated, the log showed no variables so it meant that the %IF condition was not working.

options mprint symbolgen;

%macro m;
%let dsid = %sysfunc(open(sashelp.class));

proc sql noprint;
	create table t1 as
	 select 

	 %do i =  1 %to  5;

	  %if %sysfunc(vartype(&dsid, &i)) = C %then %put  %sysfunc(varname(&dsid, &i)) ,;

	  %end;

	 from sashelp.class;
quit;

%mend m;
%m;

[SAS_inquisitive · Accepted solution]

Looks like I made it  to work. Thanks all.

%macro m;

	proc sql noprint;
		create table t1 as
			select 

		%let dsid = %sysfunc(open(sashelp.class));
		%let varlist=;

		%do i=1 %to %sysfunc(attrn(&dsid, nvars));
			%if (%sysfunc(vartype(&dsid, &i))= C) %then
				%let varlist=&varlist %sysfunc(varname (&dsid, &i)) %str(,);
			%let varlist_ = %substr(&varlist.,1,%length(&varlist.)-1);
		%end;

		&varlist_


		from sashelp.class;
	quit;

%mend m;

%m