topic Dynammic macro variables to select data in SAS Programming

Dynammic macro variables to select data

drfzLee — Fri, 21 Oct 2022 08:18:15 GMT

Hello,

I need some help with my program.

I have a list of x therapies (thlist) in one variable. Now I want to create a macro variable (since thlist can change over time) that I can use in a dataset (data01) to exclude all data that has the therapy listet in thlist.

So far i got this:

*this is my therpylist;

data thlist;
set have;
run;

--> looks like this:

var therapy

1 th1

2 th2

3 th3

...

*Now create a macrolist;

data macrolist;
set thlist end=last;
call symput("norpt", therapy);
if last then call symput("numnorpt", _n_);
run;

*%put total No of no reports: &numnorpt;
*%put no report Therapien: &norpt1;

Following step doesnt work!

*replace hardcoding, creating macro;
options mtrace mprint;
%Macro norptlist;
data norpt_&&norpt&i;
set macrolist;
%do i =1 %to &numorigins;
where nr = "&&norpt&i";
%end;
run;

%Mend norptlist;
%norptlist;

When I have the macrovariable I want to delete all data that have the therapy listed in my macrolist:

I would do this:

DATA new;
SET data;
if therapy eq &&norpt&i then delete;
RUN;

Would appreciate some help on this.

Thx

Re: Dynammic macro variables to select data

Kurt_Bremser — Fri, 21 Oct 2022 08:40:31 GMT

No macro coding needed.

proc sql;
create table want as
  select *
  from data
  where therapy not in (select therapy from thlist)
;
quit;

If performance is of importance, use a data step hash object:

data want;
set data;
if _n_ = 1
then do;
  declare hash t (dataset:"thlist");
  t.definekey("therapy");
  t.definedone();
end;
if t.check() ne 0; /* not found */
run;

Untested, posted from my tablet.

Re: Dynammic macro variables to select data

Kurt_Bremser — Fri, 21 Oct 2022 10:24:56 GMT

Just for completeness, the "classic" sort/data step method:

proc sort data=thlist;
by therapy;
run;

proc sort data=data;
by therapy;
run;

data want;
merge
  data
  thlist (in=t)
;
by therapy;
if not t;
run;

Re: Dynammic macro variables to select data

drfzLee — Wed, 26 Oct 2022 10:56:22 GMT

Thank you Kurt for your hints. I created a macro variable via proc sql. It works well for me.

Proc sql;
select therapy into :threp separated by ' ' from have
quit;
%put &threp;

DATA new;
SET data;
if therapy in (&threp.) then delete;
RUN;

Re: Dynammic macro variables to select data

Kurt_Bremser — Wed, 26 Oct 2022 13:00:44 GMT

This is a very brittle algorithm. Once your have dataset overflows the 64K limit of macro variables, it will fail.

The data step MERGE method is immune to this (it will work until you run out of disk space, of which you probably have terabytes).

Bottom line: don't use your method in anything meant for production use.