multiple variables if in any DS in a specific library then append them.

[RTelang]

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...?

[Patrick · Accepted solution]

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;

[RTelang]

@Patrick can i incorporate macro parameter like varlist=(name,age,stat_details) in ur code so even if any 1 or 2 or all variable are there in the ds in that library they will be appended...?

[Patrick]

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.

[RTelang]

@Patrick am asking can we make the variable parameter generic, any number of variables can be in the parameter and the DS having those variables should be appended, and also if some DS have 1 variable some have 3 variables from the parameter varlist or some DS having all variables from the parameter varlist they all need to append.

[Patrick]

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.

[RTelang]

@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: ;, !, !!, &, (

[Patrick]

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.

[RTelang]

@Patrick can u suggest a edit how to resolve it to a string..plz?

[Patrick]

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