topic Re: Problem to retrieve column names using proc sql in SAS Enterprise Guide

Problem to retrieve column names using proc sql

ctisseuil — Tue, 27 Apr 2021 07:14:48 GMT

Hi,

I do not manage to get column names from my dataset using proc sql.

What's wrong in the following example code ?

data work.birthday;
  input @01 employee_id   6.
        @08 last_name     $10.
        @19 birthday      date7.;
  format employee_id   6.
         last_name     $10.
         birthday      date7.;
  datalines;
  1247 Garcia     04APR54
  1078 Gibson     23APR36
  1005 Knapp      06OCT38
  1024 Mueller    17JUN53
;
proc print data=work.birthday;
  title2 'SAS Data Set work.BIRTHDAY';
run;

proc sql;
select * into :charlist separated by ' '
from dictionary.columns
where libname="work" and memname="birthday" 
;
quit;

Re: Problem to retrieve column names using proc sql

FreelanceReinh — Tue, 27 Apr 2021 08:40:32 GMT

Hi @ctisseuil,

Write "select name ..." instead of "select * ..."
Specify libname and memname values in UPPER CASE in the WHERE clause.

Re: Problem to retrieve column names using proc sql

Patrick — Tue, 27 Apr 2021 09:46:09 GMT

And adding to @FreelanceReinh : The values for libname and memname are always uppercase

where libname="WORK" and memname="BIRTHDAY"

Re: Problem to retrieve column names using proc sql

Tom — Tue, 27 Apr 2021 14:22:56 GMT

Do you want the full list of problems? Or just the problems with the part that is trying to gather variable names?

Let's start with the last step:

proc sql;
select * into :charlist separated by ' '
from dictionary.columns
where libname="work" and memname="birthday" 
;
quit;

Issues:

Add the NOPRINT option to the PROC SQL so the names are not also printed to the output window.
Specify the actual value you want to put into the macro variable. This would only select the variable NAME if that happens to be the first variable that would get selected by the * shortcut for all varaibles.
Values of LIBNAME and MEMNAME are always uppercase in the DICTIONARY.COLUMNS.
Do you care what order the names appear in the list?
You also need to protect in case VALIDVARNAME option is set to ANY. That means some names might have spaces in them. You can use the NLITERAL() function for example Or use some other character as the separator in the list for values.

proc sql noprint;
select nliteral(name)
  into :charlist separated by ' '
  from dictionary.columns
  where libname="WORK" and memname="BIRTHDAY"
  order by varnum 
;
quit;