BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MsGeritO
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
IanWakeling
Barite | Level 11

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

2 REPLIES 2
IanWakeling
Barite | Level 11

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
Obsidian | Level 7
Thank you for your help. I trust your judgement and will apply accordingly.

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 2 replies
  • 1089 views
  • 3 likes
  • 2 in conversation