topic How do I solve this problem ? in SAS Programming

How do I solve this problem ?

sasuser_01 — Fri, 25 Nov 2022 05:32:51 GMT

Dataset1

ProductId Account_Num Flag1 Flag2 Flag3

1 89123 Y Y N

1 98571 N Y Y

2 86754 Y Y N

Dataset2

Product_Id R_Code Order

1 50 1

1 45 2

2 80 1

In Macros i have conditions

%macro 50;
if Product_Id in ('1') then do ;
if Flag1 in ('Y') then do;
status_code ='50';
end;
end;
%mend 50;

%macro 45;
if Product_Id in ('1') then do ;
if Flag2 in ('Y') then do;
status_code ='45';
end;
end;
%mend 45;

Here for example - acct_num 89123 qualify for status_code 50 and 45 as per the conditions specified in macros. But in output dataset i should only get the status_code to be updated to 50 to this particular account as it's order is 1. We have to get the status_code which is first matching based on order. In the same way all the accounts should get updated with correct status_code.

Output Dataset

Product_id Account_Num Flag1 Flag2 Flag3 Status_Code

1 89123 Y Y N 50

1 98571 N Y Y 45

2 86754 Y Y N 50

Can anyone tell me how to solve this?

Re: How do I solve this problem ?

PeterClemmensen — Fri, 25 Nov 2022 08:04:28 GMT

How many macro with logic do you have? Why is the logic contained in macros?

Re: How do I solve this problem ?

sasuser_01 — Fri, 25 Nov 2022 12:33:43 GMT

That is the requirement. We should keep conditions only in macro. And for every if condition one specific macro should be created.

Re: How do I solve this problem ?

sasuser_01 — Fri, 25 Nov 2022 12:35:05 GMT

And there are 50 plus macros .

Re: How do I solve this problem ?

SASKiwi — Fri, 25 Nov 2022 21:22:17 GMT

So is there are rule that if an account qualifies for more than one status, the highest status code is applied?

Also macro names need to be valid SAS names and they can't start with a number.

Re: How do I solve this problem ?

Astounding — Fri, 25 Nov 2022 21:21:35 GMT

The data you presented show that Product_ID and Flag2 don't exist in the same data set. How do you propose to put the data together so that a macro could be applied?

What do you want to name your macros? Clearly "50" and "45" are invalid names for a macro?

Re: How do I solve this problem ?

fja — Fri, 25 Nov 2022 21:27:31 GMT

@sasuser_01 wrote:
And there are 50 plus macros .

And let me guess, they only differ in the value to be compared to? 🙂 Using arguments to macros wouldn't be allowed?

Re: How do I solve this problem ?

sasuser_01 — Sat, 26 Nov 2022 04:23:28 GMT

Dataset 1 has product_id and flag variables. I will call macros in dataset1. And also I will change macros to m50 and m45 etc. My thought is to keep all the updated status codes in a array in output dataset. And then to select the value in an array according to the order mentioned in dataset 2.

Re: How do I solve this problem ?

fja — Sat, 26 Nov 2022 09:50:47 GMT

I do not fully understand the idea behind your macros and the intended logic. The macros given just act on product_id 1 ... what about the others? What shall be the status_code if the flag tested for is different from Y?

Have I missed something?

--fja

edit: To be more precise: Wouldn't simply merging in the first matching observation from dataset2 an alternative to the use of macros (code untested):

data outTable; 
  merge dataset1 dataset2;
  by Product_Id;
  if first.Product_Id = 1;
run;

Re: How do I solve this problem ?

Tom — Sat, 26 Nov 2022 19:13:56 GMT

I don't think you have totally explained what you are doing.

It looks like the second dataset is what is driving the logic. So let's call that dataset the RULES.

data rules;
  input Product_Id Order R_Code ;
cards;
1 1 50
1 2 45
2 1 80
;

And the first dataset seems to be the actual data. Let's call that ORDERS.

data orders ;
  input Account_Num Product_Id (Flag1-Flag2) (:$1.);
cards;
89123  1  Y Y 
98571  1  N Y 
86754  2  Y Y 
;

And now reading between the lines of your IF/THEN statements it looks like you want to use the FLAG1 status with the ORDER=1 rule. So it would be better to transpose the ORDERS dataset to have one observation per FLAG.

data orders_tall;
  set orders;
  array _flag flag1-flag2 ;
  do order=1 to dim(_flag);
     flag=_flag[order];
     output;
  end;
  drop flag1-flag2;
run;

Now we can merge the data by PRODUCT_ID and ORDER .

proc sql;
create table want as
  select a.*
       , b.r_code
  from orders_tall a 
  left join rules b
    on a.product_id=b.product_id
    and a.order = b.order 
    and a.flag='Y'
  order by a.account_num,a.product_id,a.order
;
quit;

And then roll it back up into a single observation.

data want;
  set want;
  by account_num product_id;
  retain status_code flag1-flag2;
  array _flag $1 flag1-flag2 ;
  length status_code $8 ;
  if first.product_id then call missing(of status_code _flag[*]);
  status_code=coalescec(status_code,r_code);
  _flag[order]=flag;
  if last.product_id;
  drop flag r_code order;
run;

This can easily be extended from TWO flag/order value to 50. And no macros (or macro variables) are required.