select into using sashelp.vcolumn, return a 0 record, why?

[Peter_C]

rather than research history

%macro testMAV( source, where= ) ;

proc sql ;

create table _data_ as select * from &source

   where &where

   ;

quit ;

%mend  testMAV ;

%put %quote( %testMAV(   sashelp.vcolumn, where= %str( libname= 'SASHELP' and memname like 'CL%' ) ) );

             %testMAV(   sashelp.vcolumn, where= %str( libname= 'SAMPSIO' and memname like 'EN%' ) )

             %testMAV(dictionary.columns, where= %str( libname= 'SAMPSIO' and memname like 'EN%' ) )

             %testMAV(dictionary.columns, where= %str( libname= 'SAMPSRC' and memname like 'EN%' ) )

             %testMAV(   sashelp.vcolumn, where= %str( libname= 'SAMPSRC' and memname like 'EN%' ) )

My log reported equivalent run and cpu-times after the first

Interesting effect in this data step (not expected to perform well)

101  data cols;
102   set sashelp.vcolumn( where=( libname='work' and memname like 'T_%' )) ;
103  run ;

NOTE: The map data sets in library MAPSGFK are based on the digital maps from GfK
      GeoMarketing and are covered by their Copyright. For additional information, see
      http://support.sas.com/mapsonline/gfklicense.
NOTE: There were 0 observations read from the data set SASHELP.VCOLUMN.
      WHERE (libname='work') and memname like 'T_%';
NOTE: The data set WORK.COLS has 0 observations and 18 variables.
NOTE: DATA statement used (Total process time):
      real time           43.66 seconds
      user cpu time       0.34 seconds
      system cpu time     1.06 seconds
      memory              839.26k
      OS Memory           17864.00k

It reports trying to open  library MAPSGFK

indicating that to fill this SET statement, all known libraries were examined.

I suggest using libraries SAMPSIO and SAMPSRC because although these are seldom assigned, they exist as environment variables pointing to folders hosting the SAS Sample libraries.

Even though no libname SAMPSIO was previously assigned, SQL or the dictionary engine,  will assign the libname to support the users request in the sql query

Before these queries these environment variables were not  assigned as libraries so a where= filter like

LIBNAME LIKE 'SAMPS%'

would return no rows

but the following is successful

LIBNAME EQ 'SAMPSIO'

[data_null__]

The only difference I see is the first time the "dictionary" is accessed the step takes a bit longer.

[Peter_C]

performance falls off the cliff when you use anything other than SQL (obviously then you need to use the SASHELP views) for catalogs or columns -- when many libraries are assigned.

The dictionary engine needs to fully populate the requested table - so all libraries/tables/catalogs are examined

[Ron_MacroMaven]

Data _Null_ said:

>

The only difference I see is the first time the "dictionary" is accessed the step takes a bit longer.

This factoid is the reason that I stated that accessing sashelp views takes longer.

Because, in my testing I put the test against sashelp first

I sit corrected:

the first access of the dictionaries causes them to be updated,

thus, whichever is timed first:

* sashelp views

* sql dictionary

will take longer.

I will return to my benchmark suite one of these days

to stress test this idea.

http://www.sascommunity.org/wiki/Bench_Marking_Results_Comparing_v9-1_and_v9-2

thanks, Krewe

you're making this place look like SAS-L!

LOL

[LarryWorley]

Peter,

Not sure what results you see (you're holding your cards clase to your vest).  b

But when I try what you suggest on AIX system using sas 9.2, I see not differences between dictionary and sashelp views.  I get the same results with pc-sas -- no measurable difference.

Of course this was q quick and dirty test using some code on which I am currently working.

Here is the log for everyone to examine and comment on.

Larry

NOTE: Remote submit to MARYKAY commencing.

　

1301 proc sql stimer;

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

1302

1303 create table work_peims_list as

1304 select memname

1305 ,nobs

1306 ,nvar

1307 from sashelp.vtable

1308 where libname = 'WORK'

1309 and memname like 'PEIMS%'

1310 order by memname

1311 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.WORK_PEIMS_LIST created, with 7 rows and 3 columns.

NOTE: SQL Statement used (Total process time):

real time 0.02 seconds

cpu time 0.02 seconds

1312 create table class as

1313 select *

1314 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

1315

1316 create table perm_peims_list as

1317 select memname

1318 ,nobs

1319 ,nvar

1320 from sashelp.vtable

1321 where libname = 'PERM'

1322 and memname like 'PEIMS%'

1323 order by memname

1324 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.PERM_PEIMS_LIST created, with 7 rows and 3 columns.

NOTE: SQL Statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

1325

1326 create table class as

1327 select *

1328 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

1329

1330 create table perm_drop_columns as

1331 select memname

1332 ,name

1333 from sashelp.vcolumn

1334 where libname = 'PERM'

1335 and memname like 'PEIMS%'

1336 and upcase(name) in ('STUDENTID'

1337 ,'SSN_ALTID'

1338 ,'LOCALID'

1339 ,'FNAME'

1340 ,'MNAME'

1341 ,'LNAME'

1342 ,'GEN'

1343 ,'BIRTHDATE'

1344 )

1345 order by memname

1346 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.PERM_DROP_COLUMNS created, with 14 rows and 2 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

1347

1348 create table class as

1349 select *

1350 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

1351

1352

1353 create table work_peims_list as

1354 select memname

1355 ,nobs

1356 ,nvar

1357 from dictionary.tables

1358 where libname = 'WORK'

1359 and memname like 'PEIMS%'

1360 order by memname

1361 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.WORK_PEIMS_LIST created, with 7 rows and 3 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

1362 create table class as

1363 select *

1364 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

1365

1366 create table perm_peims_list as

1367 select memname

1368 ,nobs

1369 ,nvar

1370 from dictionary.tables

1371 where libname = 'PERM'

1372 and memname like 'PEIMS%'

1373 order by memname

1374 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.PERM_PEIMS_LIST created, with 7 rows and 3 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

1375

1376 create table class as

1377 select *

1378 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

1379

1380 create table perm_drop_columns as

1381 select memname

1382 ,name

1383 from dictionary.columns

1384 where libname = 'PERM'

1385 and memname like 'PEIMS%'

1386 and upcase(name) in ('STUDENTID'

1387 ,'SSN_ALTID'

1388 ,'LOCALID'

1389 ,'FNAME'

1390 ,'MNAME'

1391 ,'LNAME'

1392 ,'GEN'

1393 ,'BIRTHDATE'

1394 )

1395 order by memname

1396 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.PERM_DROP_COLUMNS created, with 14 rows and 2 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

1397 create table class as

1398 select *

1399 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

1400

1401

1402 create table work_peims_list as

1403 select memname

1404 ,nobs

1405 ,nvar

1406 from sashelp.vtable

1407 where libname = 'WORK'

1408 and memname like 'PEIMS%'

1409 order by memname

1410 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.WORK_PEIMS_LIST created, with 7 rows and 3 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

1411 create table class as

1412 select *

1413 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

1414

1415 create table perm_peims_list as

1416 select memname

1417 ,nobs

1418 ,nvar

1419 from sashelp.vtable

1420 where libname = 'PERM'

1421 and memname like 'PEIMS%'

1422 order by memname

1423 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.PERM_PEIMS_LIST created, with 7 rows and 3 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

1424

1425 create table class as

1426 select *

1427 from sashelp.class ;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: SQL Statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

1428

1429 create table perm_drop_columns as

1430 select memname

1431 ,name

1432 from sashelp.vcolumn

1433 where libname = 'PERM'

1434 and memname like 'PEIMS%'

1435 and upcase(name) in ('STUDENTID'

1436 ,'SSN_ALTID'

1437 ,'LOCALID'

1438 ,'FNAME'

1439 ,'MNAME'

1440 ,'LNAME'

1441 ,'GEN'

1442 ,'BIRTHDATE'

1443 )

1444 order by memname

1445 ;

NOTE: SAS threaded sort was used.

NOTE: Table WORK.PERM_DROP_COLUMNS created, with 14 rows and 2 columns.

NOTE: SQL Statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

1446 quit ;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

　

NOTE: Remote submit to MARYKAY complete.

[Peter_C]

his Larry

you prove the point

there is no need to deprecate the SASHELP views

Peter

p.s.

and it has been this way since SASv6.something

Message was edited by: Peter Crawford just had to add the postscript that this isn't some recent improvement

[Ksharp]

According to documentation, sashelp is used for data step, while dictionary is used for proc sql .

Ksharp