Macros and Arrays - Manipulating data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Macros and Arrays - Manipulating data

Hello,

 

I am currently struggling when trying to manipulate data. I have data which has been ordered highest to lowest in value size and associated to this is what type of value this is. What i am trying to achieve is to across these values there are 3 match values which i would be looking to pull to the front and order by value size (this would also have to update the type as they are associated with one another), and then leave the remaining transaction to fall behind these in the order they were with the match trans removed (as these are now at the front of the data.

I have posted the example of what I am starting with and then the outcome I am trying to achieve. I have tried a macro and do looks and i just dont seem to be able to get the correct outcome.

data original;

input type_1 $ type_2 $ type_3 $ type_4 $ type_5 $ type_6 $ num_1 num_2 num_3 num_4 num_5 num_6

 

match_1 match_2 match_3;

id=_n_;

datalines;

 

A C A B B A 12 9 8 8 7 6 7 11 44

B A C C A B 11 10 10 7 2 1 2 7 3

B B B A C B 10 9 8 7 6 1 1 9 10

B A C C A C 4 3 3 2 1 1 16 5 2

;

run;

 

The code shows type and value ordered 1 to 6 and the final 3 numbers are the matching numbers we are trying to pull out to put to the front of num 1 to 6.

 

The outcome is shown below:

data output;

input type_1 $ type_2 $ type_3 $ type_4 $ type_5 $ type_6 $ num_1 num_2 num_3 num_4 num_5 num_6

 

match_1 match_2 match_3;

id=_n_;

datalines;

 

B A C A B A 7 12 9 8 8 6 7 11 44

C A B A C B 7 2 11 10 10 1 2 7 3

B B B B A C 10 9 1 8 7 6 1 9 10

C B A C A C 2 4 3 3 1 1 16 5 2

run;

 

As you can see the code now has reorder the num and type 1 - to six pulling out the numbers which match the match variables and has ordered these in value size as well as moving the associated type id to keep these aligned.

 

Any help would be great as i cant appear to get to this outcome.

 

Thank you,

Michael


Accepted Solutions
Solution
Friday
Super User
Posts: 10,209

Re: Macros and Arrays - Manipulating data

Posted in reply to Mick_bill

Let me rephrase your issue:

In obs 1, you find that the fifth number corresponds to one of the numbers in match_1-match_3, therefore you move it forward (moving everything else back one place). You also move the fifth character element forward.

In obs 2, both the 4th and 5th numerical element have a match, so you move both, but order them in descending order. Corresponding character elements are moved accordingly.

In obs 3, 3 elements match and end up in the descending sequence 10-9-1 in the front, although two don't need to be moved.

In obs 4, only the 4th element matches, and is moved forward. So is the 4th character element.

 

You won't need any macro code for this, as it is purely a matter of manipulating data (and not program code, which is what macros are for).

Define two arrays (character and numeric) for the first two variable groups, and a third one for match_1-match_3.

Then loop through and replace if match is found, remembering how many replacements you've already made, and at the end do a sort of the replaced elements.

 

Here an example I've come up with. Note that the sort algorithm is a quite crude one that needs to be improved for arbitrary large numbers of possible replacements.

data original;
input
  type_1 $ type_2 $ type_3 $ type_4 $ type_5 $ type_6 $
  num_1 num_2 num_3 num_4 num_5 num_6
  match_1 match_2 match_3
;
id=_n_;
datalines;
A C A B B A 12 9 8 8 7 6 7 11 44
B A C C A B 11 10 10 7 2 1 2 7 3
B B B A C B 10 9 8 7 6 1 1 9 10
B A C C A C 4 3 3 2 1 1 16 5 2
;
run;

data want;
set original;
array chars {*} type_1-type_6;
array nums {*} num_1-num_6;
array match {*} match_1-match_3;
replaced = 0;
do index = 1 to dim(nums);
  found = 0;
  do i = 1 to dim(match);
    if nums{index} = match{i} then found = 1;
  end;
  if found
  then do;
    mynum = nums{index};
    mychar = chars{index};
    replaced + 1;
    if index ne 1 then do i = index to replaced + 1 by -1;
      nums{i} = nums{i-1};
      chars{i} = chars{i-1};
    end;
    nums{replaced} = mynum;
    chars{replaced} = mychar;
  end;
end;
if replaced ne 0
then do i = 1 to replaced;
  do j = 1 to i-1;
    if nums{j} < nums{i}
    then do;
      mynum = nums{i};
      mychar = chars{i};
      nums{i} = nums{j};
      chars{i} = chars{j};
      nums{j} = mynum;
      chars{j} = mychar;
    end;
  end;
end;
drop index i j mynum mychar replaced found;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
Friday
Super User
Posts: 10,209

Re: Macros and Arrays - Manipulating data

Posted in reply to Mick_bill

Let me rephrase your issue:

In obs 1, you find that the fifth number corresponds to one of the numbers in match_1-match_3, therefore you move it forward (moving everything else back one place). You also move the fifth character element forward.

In obs 2, both the 4th and 5th numerical element have a match, so you move both, but order them in descending order. Corresponding character elements are moved accordingly.

In obs 3, 3 elements match and end up in the descending sequence 10-9-1 in the front, although two don't need to be moved.

In obs 4, only the 4th element matches, and is moved forward. So is the 4th character element.

 

You won't need any macro code for this, as it is purely a matter of manipulating data (and not program code, which is what macros are for).

Define two arrays (character and numeric) for the first two variable groups, and a third one for match_1-match_3.

Then loop through and replace if match is found, remembering how many replacements you've already made, and at the end do a sort of the replaced elements.

 

Here an example I've come up with. Note that the sort algorithm is a quite crude one that needs to be improved for arbitrary large numbers of possible replacements.

data original;
input
  type_1 $ type_2 $ type_3 $ type_4 $ type_5 $ type_6 $
  num_1 num_2 num_3 num_4 num_5 num_6
  match_1 match_2 match_3
;
id=_n_;
datalines;
A C A B B A 12 9 8 8 7 6 7 11 44
B A C C A B 11 10 10 7 2 1 2 7 3
B B B A C B 10 9 8 7 6 1 1 9 10
B A C C A C 4 3 3 2 1 1 16 5 2
;
run;

data want;
set original;
array chars {*} type_1-type_6;
array nums {*} num_1-num_6;
array match {*} match_1-match_3;
replaced = 0;
do index = 1 to dim(nums);
  found = 0;
  do i = 1 to dim(match);
    if nums{index} = match{i} then found = 1;
  end;
  if found
  then do;
    mynum = nums{index};
    mychar = chars{index};
    replaced + 1;
    if index ne 1 then do i = index to replaced + 1 by -1;
      nums{i} = nums{i-1};
      chars{i} = chars{i-1};
    end;
    nums{replaced} = mynum;
    chars{replaced} = mychar;
  end;
end;
if replaced ne 0
then do i = 1 to replaced;
  do j = 1 to i-1;
    if nums{j} < nums{i}
    then do;
      mynum = nums{i};
      mychar = chars{i};
      nums{i} = nums{j};
      chars{i} = chars{j};
      nums{j} = mynum;
      chars{j} = mychar;
    end;
  end;
end;
drop index i j mynum mychar replaced found;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: Macros and Arrays - Manipulating data

Posted in reply to KurtBremser

thank you this works great and makes sense about not using a macro and keeping it as an array.

 

thanks,

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 70 views
  • 0 likes
  • 2 in conversation