BookmarkSubscribeRSS Feed
ZRick
Obsidian | Level 7

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

21 REPLIES 21
Tom
Super User Tom
Super User

What is OUTLIB for in the macro header?

Try calling it like this

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

ZRick
Obsidian | Level 7

I tried to this macro:

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

It still says:

NOTE: No rows were selected.

Tom
Super User Tom
Super User

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

art297
Opal | Level 21

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

UrvishShah
Fluorite | Level 6

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

Ron_MacroMaven
Lapis Lazuli | Level 10

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;
data_null__
Jade | Level 19

Ronald  Fehd wrote:

btw:

sashelp views are deprecated:

Where is that documented?

Ron_MacroMaven
Lapis Lazuli | Level 10

"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

art297
Opal | Level 21

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

art297
Opal | Level 21

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

Ron_MacroMaven
Lapis Lazuli | Level 10

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!

Peter_C
Rhodochrosite | Level 12

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

data_null__
Jade | Level 19

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.

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
  • 21 replies
  • 1776 views
  • 9 likes
  • 9 in conversation