%IF (condition1) and (condition2) %then %do...

[saskapa]

Hi,

I am coding a little sas macro that has an %IF statement with two condition to be tested....Unfortunatly the condtion tested is FLASE,I don't understand why because both are true !  So I guess that my way of testing the two condition are not proper to %if statement...Any clue ?

Thanks in advance

saskap

Here is an example :

option mprint mlogic symbolgen;
%macro test/minoperator;
%global listvar;
proc sql;
select distinct compress(quote(name)) into :listvar separated by ','
from sashelp.vcolumn
where libname="SASHELP" and memname="DEMOGRAPHICS";

%let a="NAME";
%LET b="ISONAME";

%if  (&a in ( &listvar.))  and (&b in  ( &listvar.)) %then %do;
create table TEST as
select  NAME,ISONAME
from sashelp.demographics;
%end;
quit;

%mend test;

%test

[JoshB · Accepted solution]

The default macro in list delimiter is a blank space. You should be able to modify the 

separated by "," 

to 

separated by " "

Alternatively, you can specify the comma as the delimiter by modifying your options statement to

option mprint mlogic symbolgen mindelimiter=",";

[RW9]

Not entirely sure why you need the macro there, base SAS can do most of these things without resorting to macro, for instance:

%let a="NAME";
%let b="ISONAME";

proc sql;
  create table TEST as
  select  NAME,ISONAME
  from    SASHELP.DEMOGRAPHICS
  where   &A. in (select distinct NAME from DICTIONARY.COLUMNS where LIBNAME="SASHELP" and MEMNAME="DEMOGRAPHICS")
    and   &B. in (select distinct NAME from DICTIONARY.COLUMNS where LIBNAME="SASHELP" and MEMNAME="DEMOGRAPHICS");
quit;

Will always create an output dataset TEST, but will only have records if the condition is true.  As we know the dataset always gets created, we don't need to check that.  For example and further code run on the dataset TEST - in your methodology you would need to check the existence of the file each time - in this way we know the file is always going to be there, but no obs will come out, hence the code will always run.

[saskapa]

Hi RW9,

Yes indeed, and interesting...

I need a macro, because in my case (see below ) I want something  generic where AE dataset may or may not have all the variables (depending on studies ) . Your proposal is also a solution and looks more elegant....

%macro test(lib=)/ minoperator;

%global listvar;


proc sql;
select distinct quote(compress(name)) into :listvar separated by ' '
from sashelp.vcolumn
where libname="%upcase(&lib.)" and memname="AE";
quit; 


proc sql;

*1. Check for xxterm vs xxcod (term,prefered_term);

%if AEDECOD in ( &listvar.) and AEBODSYS in  ( &listvar.) %then %do;

create table ae_uniq_decod_bodsys as
select  AEDECOD,AEBODSYS
from ae
group by AEDECOD
having (count( distinct AEBODSYS) gt 1 ) ;

%end;

%if AEDECOD in ( &listvar.) and AESOC in  ( &listvar.) %then %do;

create table ae_uniq_decod_bodsys as
select  AEDECOD,AESOC
from ae
group by AESOC
having (count( distinct AESOC) gt 1 ) ;

%end;


%if AESOC in ( &listvar.) and AESOCCD in  ( &listvar.) %then %do;

create table ae_uniq_decod_bodsys as
select AESOC,AESOCCD
from ae
group by AESOC
having (count( distinct AESOCCD) gt 1 ) ;
%end;

...same logic for the cases below...

create table ae_uniq_llt_lltcd as
select  AELLT,AELLTCD
from ae
group by AELLT
having (count( distinct AELLTCD) gt 1 ) ;


create table ae_uniq_decod_ptcod as
select   AEDECOD,AEPTCD
from ae
group by  AEDECOD
having (count( distinct AEPTCD) gt 1 ) ;

create table ae_uniq_bodsys_bodsyscd as
select   AEBODSYS,AEBODSYSCD
from ae
group by  AEBODSYS
having (count( distinct AEBODSYSCD) gt 1 ) ;


quit;

%mend test;

Here data will only be created if condition is true...

The previous example was written to understand why it didn't work as expected....

Thanks 

saskapa

[RW9]

Well, firstly, why is your AE data changing each time?  Standard CRF -> Standard Dataset -> Standard SDTM dataset etc.  

Secondly, if you know those variables are fixed, and may or may not exist then do:

data loop;
  set sashelp.vcolumn (where=(libname="WORK" and memname="AE"
                                                  and name in ("AEBODSYS","AESCOC","AESOCCD"...));
  call execute('proc sql; create table AE_DISTINCT_'||strip(name)||' as select distinct AEDECOD,'||strip(name)||' from WORK.AE; quit;');
run;

Thus, the code will, for each existing variable in the given list, generate the proc sql code to generate the dataset.  Saves typing all that each time.  Although saying that, why not just use one of the procedures, proc freq for instance, and just get each combination of distinct variables, something like (and not at work so can't check):

proc freq data=work.ae out=tmp;
run;

That should give you all the distinct value / pairs and you can take your lists from one dataset then.