Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: Macros and Arrays - Manipulating data

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-13-2018 05:07 AM
(365 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

thanks,

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

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.