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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

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;
Mick_bill
Fluorite | Level 6

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

 

thanks,

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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