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