DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 133

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

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 );

Super User
Super User
Posts: 7,042

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

What is OUTLIB for in the macro header?

Try calling it like this

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

Frequent Contributor
Posts: 133

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

I tried to this macro:

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

It still says:

NOTE: No rows were selected.

Super User
Super User
Posts: 7,042

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

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);

PROC Star
Posts: 7,471

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

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

Regular Contributor
Posts: 195

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

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

Regular Contributor
Posts: 227

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

btw:

sashelp views are deprecated:

use

dictionary.columns

or

dictionary.tables

proc sql; describe view sashelp.vcolumn;

          describe view sashelp.vtable;

          quit;

7proc 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;
Respected Advisor
Posts: 3,799

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

Posted in reply to Ron_Fehd_macro_maven

Ronald  Fehd wrote:

btw:

sashelp views are deprecated:

Where is that documented?

Regular Contributor
Posts: 227

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

Posted in reply to data_null__

"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

PROC Star
Posts: 7,471

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

Posted in reply to Ron_Fehd_macro_maven

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

Super User
Super User
Posts: 7,042

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

PROC Star
Posts: 7,471

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

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

Regular Contributor
Posts: 227

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

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!

Valued Guide
Posts: 2,177

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

Posted in reply to Ron_Fehd_macro_maven

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

Respected Advisor
Posts: 3,799

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

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.

Ask a Question
Discussion stats
  • 21 replies
  • 513 views
  • 9 likes
  • 9 in conversation