topic Re: Extraction from SASHELP.VCLOUMN is hanging for long time in SAS Programming

Extraction from SASHELP.VCLOUMN is hanging for long time

bmsampath — Mon, 02 Jan 2017 17:31:13 GMT

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

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

Cynthia_sas — Mon, 02 Jan 2017 17:45:14 GMT

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

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

Reeza — Mon, 02 Jan 2017 17:46:13 GMT

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.

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

bmsampath — Mon, 02 Jan 2017 18:04:16 GMT

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.

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

Tom — Mon, 02 Jan 2017 18:31:02 GMT

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;

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

Kurt_Bremser — Tue, 03 Jan 2017 00:18:07 GMT

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.

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

ballardw — Tue, 03 Jan 2017 17:06:33 GMT

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

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

bmsampath — Tue, 03 Jan 2017 17:10:17 GMT

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

Thanks everyone.