IML uniqueby with multiple groups

[MsGeritO]

Hello!

I received a very helpful solution on how to combine an iterative do loop with conditions.

https://communities.sas.com/t5/SAS-IML-Software-and-Matrix/Search-for-multiple-references-across-two...

In the process I had been pointed towards UNIQUEBY. I managed to solve this for a single condition. It, indeed, runs a lot quicker than the previous solution unique-loc. But, again, adding a second condition is so far unsolved or deliveres wrong results. For each ID I would like the sum of euro for dummy1 = 1. Where am I going wrong?

Of course, I could create new variables (newvar = id + dummy1) and then run the programme over this variable, but that is not preferred.

Here is the part which is fine (one condition):

data somedata;
input id euro dummy1 dummy2;
cards;
1 12 0 1
1 23 1 1
1 56 1 1
1 23 1 0
2 22 0 1
2 24 1 1
2 34 1 1
2 10 1 0
3 19 0 1
3 28 1 1
3 56 1 1
3 21 1 0
4 21 0 1
4 34 1 1
4 32 1 1
4 43 1 0
;
run;

proc iml;

   use work.somedata;

   read all;

   call sortn(id);

   uqid = uniqueby(id,1);

   totalcost = j(nrow(uqid),1);

   uqid = uqid // (nrow(id)+1);

   do i = 1 to nrow(uqid)-1;

        idx = uqid[i]:(uqid[i+1]-1);

        totalcost[i] = sum(euro[idx]);

   end;

   print totalcost;

quit;

I took guesses to include dummy1 into the sorting and the uqid, but gained no correct calculations.

Example:

   call sortn(dummy1, id); *no error message, but does it do the right thing?;

   uqid = uniqueby({dummy1 id},1); *gives back one result for all;

   uqid = uniqueby(dummy1 id,1); *gives back error message;

I appreciate any help.

Thank you in advance.

Gerit

[IanWakeling · Accepted solution]

I think the uniqueby method might get too complicated when you move to more than one condition.   I suggest you create new versions of the euro variable as follows:

  euro_d1 = euro#dummy1;
  euro_d2 = euro#dummy2;
  euro_d12 = euro#dummy1#dummy2;

so these will have the amount of money where the dummy (or both dummies) are set, and zero everywhere else.   You can then total up the cost using the single condition uniqueby approach.

[MsGeritO]

Thank you for your help. I trust your judgement and will apply accordingly.