To see if specific multiple variables exist in a library?
for example in the code below i am checking if a column name is in the dataset in a specific library then append.
proc sql noprint ;
select distinct catx(".",libname,memname) into : DataList separated by " "
from dictionary.columns
where libname = upcase(temp) and upcase(age) = upcase(age);
quit;
But if the variables name, age, stat_details all these column variable are in any of the datasets in the respective library then append them.
even if any 1 or any two of the (variable name, age, stat_details) is present in the ds in a library then append. how do iaccomplish this? in my above code where it checks a single variable 'age' in 'temp' library...?
proc sql noprint;
select distinct catx(".",libname,memname) into : DataList separated by " "
from dictionary.columns
where libname = 'TEMP' and upcase(name) in ('NAME','AGE','STAT_DETAILS')
;
quit;
proc sql noprint;
select distinct catx(".",libname,memname) into : DataList separated by " "
from dictionary.columns
where libname = 'TEMP' and upcase(name) in ('NAME','AGE','STAT_DETAILS')
;
quit;
Not sure what you're asking for.
The "in" operator work like an OR condition and is logically the same like:
where libname='TEMP' and ( upcase(name)='NAME' OR upcase(name)='AGE' OR upcase(name)='STAT_DETAILS' )
" so even if any 1 or 2 or all variable are there in the ds in that library they will be appended"
Yes, if any of the variables exist in the data set, it will get selected.
You have a DISTINCT in your SQL query so the combination of {libname,memname} will get added exactly once to the resulting macro variable no matter if there had been a match to only one or multiple of your variables in a specific data set.
You can do whatever you want. Give it a go and start coding. Come back here if you get stuck with actual code you've developed.
@Patrick i tried this below code
%macro sap(lib=,varlist=);
proc sql noprint;
select distinct catx(".",libname,memname) into : datalist separated by " "
from dictionary.columns
where libname = %upcase("&lib") and %upcase(&varlist) = %upcase("&varlist");
quit;
data test1;
set &datalist;
run;
proc print data=work.test1;run;
%mend;
%sap(lib=sap,varlist=name ID height);
I did this in the library sap there are 2 DS's with all 3 varlist variables but
it appends only data from 1 DS and not the data from second DS....
and gives a this error--->
Line generated by the macro function "UPCASE".
1 NAME ID HEIGHT
--
22
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (
You should always try and make your code work without macro stuff involved. Only once that done make the code dynamic by "macrotizing" it.
You have below bit in your code:
and %upcase(&varlist) = %upcase("&varlist")
What you actually would want it to resolve to is:
and upcase(name) in ('NAME','ID','HEIGHT')
So: change your code accordingly and then pass in the parameter value in a way so that macro variable &varlist resolves to a string as required.
Below some working code doing what you have been asking for. I'm not sure though how useful this will be and if you execute the macro with the parameter values as set in below code then you'll see one of the problems you'll be facing.
Going forward it would be good if you could sometimes demonstrate a bit more of your own effort before just asking for the solution. Given where you are at currently I'd also suggest that you get first a bit more used to regular SAS coding before diving too deep into SAS macro coding.
%macro sap(lib=,varlist=);
proc sql noprint;
select distinct catx(".",libname,memname) into : datalist separated by " "
from dictionary.columns
where
libname = 'SASHELP'
and findw("&varlist",strip(name),' ','i')>0
and memtype='DATA'
;
quit;
data test1;
set &datalist;
run;
proc print data=work.test1;
run;
%mend;
%sap(lib=sashelp,varlist=name ID height);
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.