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 );
What is OUTLIB for in the macro header?
Try calling it like this
%T(dataset=sashelp.class,varname=age);
I tried to this macro:
%t(outlib=work, dataset=sashelp.class, varname=age);
It still says:
NOTE: No rows were selected.
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);
Without spending anytime really looking at your code, you declare outlib as a named parameter in your macro, but never use it.
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
btw:
sashelp views are deprecated:
use
dictionary.columns
or
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; |
Ronald Fehd wrote:
btw:
sashelp views are deprecated:
Where is that documented?
"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
@Ron: is that the new definition of a self-fulfilling profecy? I wrote it, then read it, therefore it is?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.