IML uniqueby with multiple groups

Solved
Occasional Contributor
Posts: 13

IML uniqueby with multiple groups

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;

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.

Gerit

Accepted Solutions
Solution
‎08-02-2017 03:41 AM
Regular Contributor
Posts: 168

Re: IML uniqueby with multiple groups

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.

All Replies
Solution
‎08-02-2017 03:41 AM
Regular Contributor
Posts: 168

Re: IML uniqueby with multiple groups

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.

Occasional Contributor
Posts: 13