topic Re: select into using sashelp.vcolumn, return a 0 record, why? in SAS Programming

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

ZRick — Thu, 21 Feb 2013 00:51:41 GMT

Why following macro return 0 records? how do I fix it?

%macro t (outlib=, dataset=, varname=);

%if %INDEX(&dataset,.) eq 0 %then %do;

%let libname = WORK ;

%let setname = &dataset ;

%end;

%else %do;

%let libname = %SUBSTR(&dataset,1,%INDEX(&dataset,.)-1);

%let setname = %SUBSTR(&dataset,%INDEX(&dataset,.)+1) ;

%end;

proc sql noprint;

select type into :vartype

from sashelp.vcolumn

where libname = upcase("&libname")

and memname = upcase("&setname")

and memtype = "data"

and upcase(name) = upcase("&varname")

;

quit;

%mend;

%t(outlib=sashelp, dataset=class, varname=age );

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

Tom — Thu, 21 Feb 2013 00:59:48 GMT

What is OUTLIB for in the macro header?

Try calling it like this

%T(dataset=sashelp.class,varname=age);

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

art297 — Thu, 21 Feb 2013 01:02:54 GMT

Without spending anytime really looking at your code, you declare outlib as a named parameter in your macro, but never use it.

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

ZRick — Thu, 21 Feb 2013 01:24:17 GMT

I tried to this macro:

%t(outlib=work, dataset=sashelp.class, varname=age);

It still says:

NOTE: No rows were selected.

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

Tom — Thu, 21 Feb 2013 01:43:27 GMT

MEMTYPE will never equal 'data'. It is uppercase.

But why would you care whether MEMTYPE = 'DATA' or 'VIEW'?

Also for efficiency use %upcase("&libname") rather than upcase("&libname") that way the upcase function is called once during macro processing rather than for every row examined from VCOLUMN.

%macro t (dataset, varname);

%local libname setname ;

%let libname=%upcase(%scan(&dataset,1,.));

%let setname=%upcase(%scan(&dataset,2,.));

%if &setname = %then %do;

%let setname=&libname;

%let libname = WORK ;

%end;

proc sql noprint;

%let vartype=;

select type into :vartype

from dictionary.columns

where libname = "&libname"

and memname = "&setname"

and upcase(name) = %upcase("&varname")

;

quit;

%put &libname..&setname..&varname = &vartype ;

%mend;

%t(sashelp.class,age);

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

UrvishShah — Thu, 21 Feb 2013 07:12:09 GMT

Hi,

In your SAS Code, if you bother about selecting DATA or VIEW, then take it in a Macro Variable and note that these values are always in upparcase...

See the modified code as foolow...

%macro t (dataset=, varname=, type =);

%if %INDEX(&dataset,.) eq 0 %then %do;

%let libname = WORK ;

%let setname = &dataset ;

%end;

%else %do;

%let libname = %SUBSTR(&dataset,1,%INDEX(&dataset,.)-1);

%let setname = %SUBSTR(&dataset,%INDEX(&dataset,.)+1) ;

%end;

proc sql noprint;

select type into :vartype

from sashelp.vcolumn

where libname = %upcase("&libname")

and memname = %upcase("&setname")

and memtype = %upcase("&type.")

and upcase(name) = %upcase("&varname")

;

quit;

%mend;

%t(dataset=sashelp.class, varname=age, type =data);

Thanks,

Urvish

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

Ron_MacroMaven — Tue, 23 Apr 2013 12:25:13 GMT

btw:

sashelp views are deprecated:

use

dictionary.columns

dictionary.tables

proc sql; describe view sashelp.vcolumn;

describe view sashelp.vtable;

quit;

7	proc sql;
7 !	describe view sashelp.vcolumn;

NOTE: SQL view SASHELP.VCOLUMN is defined as:

	select *
	from DICTIONARY.COLUMNS;
8	describe view sashelp.vtable;

NOTE: SQL view SASHELP.VTABLE is defined as:

	select *
	from DICTIONARY.TABLES;
9	quit;

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

data_null__ — Tue, 23 Apr 2013 12:35:58 GMT

Ronald Fehd wrote:

btw:

sashelp views are deprecated:

Where is that documented?

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

Ron_MacroMaven — Tue, 23 Apr 2013 17:44:31 GMT

"I read it on the internet."

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0812B&L=sas-l&P=R3070

check the difference in times needed to do the same process

accessing sashelp views,

since they have to have the sql dictionary tables updated

before they can have something to view,

can take an order of magnitude longer.

well, my post was written in 2008

v9 has obviously had a great deal of improvement since then

as I see no consistent differences in times in this program

%macro copy1(data=class);

proc copy in = sashelp

out = work

memtype = data;

select &data;

run;

%mend;

%copy1()

%Macro select(data=);

proc sql; select * from &data;

quit;

run;

%mend;

%select(data=sashelp.vtable)

%copy1(data=heart)

%select(data=dictionary.tables)

%copy1(data=zipcode)

%select(data=dictionary.tables)

%copy1(data=shoes)

%select(data=sashelp.vtable)

thanks for asking for a Reality Check

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

art297 — Tue, 23 Apr 2013 18:00:03 GMT

@Ron: is that the new definition of a self-fulfilling profecy? I wrote it, then read it, therefore it is?

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

Tom — Tue, 23 Apr 2013 18:06:04 GMT

http://xkcd.com/978/

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

art297 — Tue, 23 Apr 2013 18:27:25 GMT

: We really ought to go lite on this Ron guy .. after all he IS just a novice! He'll learn.

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

Ron_MacroMaven — Tue, 23 Apr 2013 18:42:34 GMT

LOL , tom,

it's a basic idea of negotiations:

"Hey, this is not my Have-To, Someone Else requires/expects this!"

Thanks for keeping me up.to-date.

Yesterday's tests were run on earlier versions

and those busy beavers at SAS Institute keep on Changing Things!

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

Peter_C — Tue, 23 Apr 2013 18:55:34 GMT

Ron

sorry, but I think you might be wrong

If instead of using the COPY procedure you use SQL to access SASHELP.V{table} with some where filters, I think you'll change your opinion

hope your experience will not vary too much from mine.

If you want to know why, respond with your metrics on SASHELP.V{table} in SQL, and I'll offer some more info

Peter

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

data_null__ — Tue, 23 Apr 2013 19:56:30 GMT

I would be interested in your research in this area I don't have a good notion of where performance differs with these seemingly similar objects.

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

LarryWorley — Tue, 23 Apr 2013 20:32:59 GMT

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.

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

Peter_C — Tue, 23 Apr 2013 20:38:32 GMT

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'

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

Peter_C — Tue, 23 Apr 2013 20:52:14 GMT

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

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

data_null__ — Tue, 23 Apr 2013 21:20:12 GMT

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

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

Peter_C — Tue, 23 Apr 2013 22:14:26 GMT

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