BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bmsampath
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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

Reeza
Super User

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. 

 

 

bmsampath
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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;

 

Kurt_Bremser
Super User

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.

ballardw
Super User

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

bmsampath
Obsidian | Level 7

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

 

Thanks everyone.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1842 views
  • 5 likes
  • 6 in conversation