Statistical programming, matrix languages, and more

IML uniqueby with multiple groups

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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;

   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]Smiley Saduqid[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

 


Accepted Solutions
Solution
3 weeks ago
Frequent Contributor
Posts: 136

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.

 

View solution in original post


All Replies
Solution
3 weeks ago
Frequent Contributor
Posts: 136

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: 11

Re: IML uniqueby with multiple groups

Thank you for your help. I trust your judgement and will apply accordingly.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 218 views
  • 3 likes
  • 2 in conversation