IML uniqueby with multiple groups

MsGeritO — Wed, 26 Jul 2017 11:09:37 GMT

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-matrices/m-p/369860#M3545

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

Re: IML uniqueby with multiple groups

IanWakeling — Wed, 26 Jul 2017 15:27:02 GMT

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.

Re: IML uniqueby with multiple groups

MsGeritO — Wed, 02 Aug 2017 07:43:58 GMT

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

topic Re: IML uniqueby with multiple groups in SAS/IML Software and Matrix Computations

IML uniqueby with multiple groups

Re: IML uniqueby with multiple groups

Re: IML uniqueby with multiple groups