Hi ,
I have the following code in my program.
proc sql NOPRINT;
select name into :col separated by "||','||" from sashelp.vcolumn where memname="COUNTRY_TRANS" and name not in("SECT_SICSID","_NAME_","_LABEL_");
quit;
The code is running in this step for long time and it isnot finishing for more than 3 hr. Is this issue related to environment?
Please advise what would have been the issue here?
Thanks,
Sampath
If you do not subset on the LIBNAME value then SAS must open every table in every library to get column attributes. You probably have many open librefs and that is why it is taking such a long time.
If you use DICTIONARY.COLUMNS and subset on LIBNAME then it can run faster. Using SASHELP.VCOLUMN will normally have worse performance since SAS does not pass the WHERE condition through the view.
proc sql NOPRINT;
select name
into :col separated by "||','||"
from DICTIONARY.COLUMNS
where libname='WORK'
and memname="COUNTRY_TRANS"
and upcase(name) not in("SECT_SICSID","_NAME_","_LABEL_")
;
quit;
But in many situations you will still get faster results by using PROC CONTENTS to query just one table at a time.
proc contents data=work.country_trans noprint out=_contents_ ;
run;
proc sql NOPRINT;
select name
into :col separated by "||','||"
from _contents_
where upcase(name) not in("SECT_SICSID","_NAME_","_LABEL_")
;
drop table _contents_ ;
quit;
Hi, I found DICTIONARY.COLUMNS to be fractionally faster than using SASHELP.VCOLUMN (which is a view of dictionary.columns). But, your mileage may vary depending on the systems involved.
If you can't figure out what the issue is, you might want to work with Tech Support.
cynthia
This happens if your connected to a database with many tables/rows. 3 hrs is extreme though.
Other options is to
1. Try dictionary.column instead of SASHELP.vcolumn
2. Use PROC contents or datasets to generate the variable list - this is usually very quick.
I tried DICTIONARY.COLUMNS also. It is also running for long time..
I will report this issue to our system admin. Thanks for your ideas. I will try theam also.
If you do not subset on the LIBNAME value then SAS must open every table in every library to get column attributes. You probably have many open librefs and that is why it is taking such a long time.
If you use DICTIONARY.COLUMNS and subset on LIBNAME then it can run faster. Using SASHELP.VCOLUMN will normally have worse performance since SAS does not pass the WHERE condition through the view.
proc sql NOPRINT;
select name
into :col separated by "||','||"
from DICTIONARY.COLUMNS
where libname='WORK'
and memname="COUNTRY_TRANS"
and upcase(name) not in("SECT_SICSID","_NAME_","_LABEL_")
;
quit;
But in many situations you will still get faster results by using PROC CONTENTS to query just one table at a time.
proc contents data=work.country_trans noprint out=_contents_ ;
run;
proc sql NOPRINT;
select name
into :col separated by "||','||"
from _contents_
where upcase(name) not in("SECT_SICSID","_NAME_","_LABEL_")
;
drop table _contents_ ;
quit;
When you use SASHELP.VCOLUMN instead of DICTIONARY.COLUMNS, SAS first executes the view (scanning all tables in all libraries) before applying the WHERE condition; given enough tables in your libraries, this can easily explain your 3 hours.
With DICTIONARY.COLUMNS, PROC SQL can optimize the table scans according to the WHERE.
I'm kind of interested in how you will use that resulting macro variable. Embedded quotes inside macro variables are often sources of headaches at later steps.
If you have the || as use in concatenation then you may well want to consider use of one of the CAT functions as || has the potential to have lots of blanks embedded in the result
Adding libname = 'WORK' in where clause solved the issue.
Thanks everyone.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.