BookmarkSubscribeRSS Feed
Almoha
Calcite | Level 5

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;

 

2 REPLIES 2
ballardw
Super User

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.

Tom
Super User Tom
Super User

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.

image.png

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
  • 2 replies
  • 672 views
  • 0 likes
  • 3 in conversation