BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RTelang
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21
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;

View solution in original post

9 REPLIES 9
Patrick
Opal | Level 21
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
Fluorite | Level 6
@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
Opal | Level 21

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
Fluorite | Level 6
@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
Opal | Level 21

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
Fluorite | Level 6

@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
Opal | Level 21

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
Fluorite | Level 6
@Patrick can u suggest a edit how to resolve it to a string..plz?
Patrick
Opal | Level 21

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

 

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!

What is Bayesian Analysis?

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.

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
  • 9 replies
  • 1386 views
  • 0 likes
  • 2 in conversation