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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
JoshB
Quartz | Level 8

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=",";

 

 

View solution in original post

4 REPLIES 4
JoshB
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

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!

How to Concatenate Values

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.

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
  • 4 replies
  • 1281 views
  • 1 like
  • 3 in conversation