topic Re: Macro Data Logic in SAS Programming

Macro Data Logic

Almoha — Fri, 01 Sep 2017 16:53:31 GMT

Hi All,

i have the following dataset

data have ;
input sub sex $ race $ trtan;
cards;
111 M AA 1
112 F AS 2
113 F W 3
114 M AS 3
;
run;

How can i generate the following lines of code based on the categorial variable supplied by the user;

Two Groups (trtan and sex)
%macro want(indsn=have , grp=trtan sex);

data want;
set have;

/*The macro should generate the following lines based the GRP parameters supplied in the macro WANT*/

if upcase(sex) = "M" and trtan=1 then trt=1;
if upcase(sex) = "M" and trtan=2 then trt=2;
if upcase(sex) = "M" and trtan=3 then trt=3;

if upcase(sex) = "F" and trtan=1 then trt=4;
if upcase(sex) = "F" and trtan=2 then trt=5;
if upcase(sex) = "F" and trtan=3 then trt=6;
run;

%macro want(indsn=have , grp=trtan sex race);

Three Groups (trtan , Race and sex)

data want;
set have;

/*The macro should generate the following lines based the GRP parameters supplied in the macro WANT*/

if upcase(sex) = "M" and trtan=1 and race = "AA" then trt=1;
if upcase(sex) = "M" and trtan=2 and race = "AS" then trt=2;
if upcase(sex) = "M" and trtan=3 and race = "W" then trt=3;

if upcase(sex) = "F" and trtan=1 and race = "AA" then trt=4;
if upcase(sex) = "F" and trtan=2 and race = "AA" then trt=5;
if upcase(sex) = "F" and trtan=3 and race = "AA" then trt=6;
run;

Re: Macro Data Logic

ballardw — Fri, 01 Sep 2017 17:50:02 GMT

While we can create code that will work for ONLY the variables you show that won't be a very "generic" macro. How would the code generator know that any specific combination gets trt=1?

For instance we have no idea what to do if you have grp= sex race. And what happens if grp = sex trtan?

I'm going to assume that

if upcase(sex) = "F" and trtan=1 and race = "AA" then trt=4;
if upcase(sex) = "F" and trtan=2 and race = "AA" then trt=5;
if upcase(sex) = "F" and trtan=3 and race = "AA" then trt=6;

has a type and you wanted to use "AS" and "W" in there somewhere but if not you need to clarify.

You should be able to describe the general rule(s) involved to program something.

This is not likely to be a short easy to understand solution after you get all of the rules specified.

Re: Macro Data Logic

Tom — Fri, 01 Sep 2017 18:37:36 GMT

Do you want to make TRT values for only the existing combinations? Or for all possible combinations? Looks like you want the latter.

%macro trtgrp(in,out,varlist);
%local i n sep var ;
%let n=%sysfunc(countw(&varlist));

proc sql ;
  create table list as 
    select *,monotonic() as trt 
    from 
%let sep=;
%do i=&n %to 1 %by -1 ;
  %let var=%scan(&varlist,&i);
  &sep (select distinct &var from &in) x&i
  %let sep=,;
%end;
  ;
  create table &out as 
    select a.*,b.trt
    from &in a, list b
    where 
%let sep=;
%do i=1 %to &n ;
  %let var=%scan(&varlist,&i);
  &sep a.&var = b.&var
  %let sep=and;
%end;
  ;
quit;
%mend trtgrp ;

Putting the sub-queries in reverse order will make the variables appear in the LIST dataset in the original order.

2056  options mprint;
2057  %trtgrp(in=have,out=want,varlist=trtan sex);
MPRINT(TRTGRP):   proc sql ;
MPRINT(TRTGRP):   create table list as select *,monotonic() as trt from (select distinct sex from have) x2
, (select distinct trtan from have) x1 ;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be
      optimized.
NOTE: Table WORK.LIST created, with 6 rows and 3 columns.

MPRINT(TRTGRP):   create table want as select a.*,b.trt from have a, list b where a.trtan = b.trtan and
a.sex = b.sex ;
NOTE: Table WORK.WANT created, with 4 rows and 5 columns.