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
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
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
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;
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;
@emrancaan I know this way. I was looking for using variable functions as I did post originally.
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.