DATA Step, Macro, Functions and more

selecting only character variables

Accepted Solution Solved
Reply
Super Contributor
Posts: 284
Accepted Solution

selecting only character variables

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

Accepted Solutions
Solution
3 weeks ago
Super Contributor
Posts: 284

Re: selecting only character variables

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

View solution in original post


All Replies
Super User
Posts: 22,850

Re: selecting only character variables

Posted in reply to SAS_inquisitive

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

 

 

 

Occasional Contributor
Posts: 8

Re: selecting only character variables

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;
Super Contributor
Posts: 284

Re: selecting only character variables

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

Contributor
Posts: 32

Re: selecting only character variables

Posted in reply to SAS_inquisitive
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;
Super Contributor
Posts: 284

Re: selecting only character variables

Posted in reply to emrancaan

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

Super User
Posts: 22,850

Re: selecting only character variables

Posted in reply to SAS_inquisitive

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;
Solution
3 weeks ago
Super Contributor
Posts: 284

Re: selecting only character variables

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 157 views
  • 5 likes
  • 4 in conversation