DATA Step, Macro, Functions and more

Extraction from SASHELP.VCLOUMN is hanging for long time

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Extraction from SASHELP.VCLOUMN is hanging for long time

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


Accepted Solutions
Solution
‎01-02-2017 11:14 PM
Super User
Super User
Posts: 7,083

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

[ Edited ]
Posted in reply to bmsampath

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;

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,869

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

Posted in reply to bmsampath

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.

use_dict_columns.png

 

  If you can't figure out what the issue is, you might want to work with Tech Support.

 

cynthia

Super User
Posts: 19,878

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

Posted in reply to bmsampath

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. 

 

 

Contributor
Posts: 32

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

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.

Solution
‎01-02-2017 11:14 PM
Super User
Super User
Posts: 7,083

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

[ Edited ]
Posted in reply to bmsampath

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;

 

Super User
Posts: 7,868

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

Posted in reply to bmsampath

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

Posted in reply to bmsampath

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

Contributor
Posts: 32

Re: Extraction from SASHELP.VCLOUMN is hanging for long time

[ Edited ]

Adding libname = 'WORK' in where clause solved the issue.

 

Thanks everyone.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 200 views
  • 2 likes
  • 6 in conversation