topic selecting only character variables in SAS Programming

selecting only character variables

SAS_inquisitive — Wed, 02 May 2018 00:51:15 GMT

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

Re: selecting only character variables

Reeza — Wed, 02 May 2018 01:00:11 GMT

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

Re: selecting only character variables

JChambo — Wed, 02 May 2018 01:02:58 GMT

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;

Re: selecting only character variables

SAS_inquisitive — Wed, 02 May 2018 12:13:26 GMT

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

Re: selecting only character variables

emrancaan — Wed, 02 May 2018 12:58:54 GMT

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;

Re: selecting only character variables

SAS_inquisitive — Wed, 02 May 2018 13:29:55 GMT

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

Re: selecting only character variables

Reeza — Wed, 02 May 2018 14:48:28 GMT

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;

Re: selecting only character variables

SAS_inquisitive — Wed, 02 May 2018 14:51:12 GMT

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