turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Convert Data comparison function from Excel Macro ...

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2018 01:57 AM

hello

i'm now trying to convert data comparing function which today i'm using Excel Macro to do, to SAS program, so that i can use for further purpose in SAS tool.

Data comparing is performed between 2 data table, one is actual data, one is template data:

**Actual Data**

Identity | Value 1 | Value 2 |

2563 | 41 | 2562700 |

2611 | 83 | 3644700 |

**Template Data**

Identity | Template 1 | Template 2 |

2342 | 22;33;35;52;91 | 2340300 |

2522 | 82;83 | 2522400;2522800 |

2524 | 27;82;83;71 | 2522800 |

2563 | 31;41 | 2560300;2562700 |

2611 | 24;82 | 2610800 |

2631 | 29 | 2631800 |

As you can see, the comparison is:

1. Search "identity" in Actual data can be found in "identity" in Template data or not, row by row until end.

2. if found, comparing Value 1 with Template 1 in that row, if found, then give figure 1, otherwise 0.

comparing Value 2 with Template 2 in that row, if found, then give figure 1, otherwise 0.

3. Perform such comparing row by row in Actual data table, until the end.

Today, the excel macro code as below:

For i = 2 To maxrow1 For j = 2 To maxrow2 CLC = Sheet1.Range("I" & i).Value FG = Sheet1.Range("F" & i).Value CLC2 = Sheet2.Range("D" & j).Value If Sheet2.Range("A" & j).Value = FG And InStr(CLC2, CLC) > 0 Then Sheet1.Range("P" & i).Value = 1 GoTo Line300 Else Sheet1.Range("P" & i).Value = 0 End If Next j Line300: Next i

I'm now struggling how to achieve such function via SAS programming coding, and would appreciate if any of you can provide some guidance here.

thank you very much in advance.

BR

Accepted Solutions

Solution

01-22-2018
08:58 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

01-18-2018 02:57 AM

Try this:

```
proc sort data=actual;
by identity;
run;
proc sort data=template;
by identity;
run;
data want;
merge
actual (in=a)
template (in=t)
;
by identity;
if a;
if not t
then do;
marker1 = .;
marker2 = .;
end;
else do;
marker1 = 0;
do i = 1 to countw(template_1,';');
if scan(template_1,i,';') = value_1 then marker1 = 1;
end;
marker2 = 0;
do i = 1 to countw(template_2,';');
if scan(template_2,i,';') = value_2 then marker2 = 1;
end;
end;
drop i;
run;
```

For tested code, supply your example datasets in data steps, see https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... (as you've already been told before).

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

All Replies

Solution

01-22-2018
08:58 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

01-18-2018 02:57 AM

Try this:

```
proc sort data=actual;
by identity;
run;
proc sort data=template;
by identity;
run;
data want;
merge
actual (in=a)
template (in=t)
;
by identity;
if a;
if not t
then do;
marker1 = .;
marker2 = .;
end;
else do;
marker1 = 0;
do i = 1 to countw(template_1,';');
if scan(template_1,i,';') = value_1 then marker1 = 1;
end;
marker2 = 0;
do i = 1 to countw(template_2,';');
if scan(template_2,i,';') = value_2 then marker2 = 1;
end;
end;
drop i;
run;
```

For tested code, supply your example datasets in data steps, see https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... (as you've already been told before).

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

01-18-2018 03:57 AM

You can simplify your code quite a lot by using the FINDW function.

Instead of

```
marker1 = 0;
do i = 1 to countw(template_1,';');
if scan(template_1,i,';') = value_1 then marker1 = 1;
end;
marker2 = 0;
do i = 1 to countw(template_2,';');
if scan(template_2,i,';') = value_2 then marker2 = 1;
end;
```

you can just do

marker1=findw(template_1,value_1,';')>0; marker2=findw(template_2,value_2,';')>0;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

01-18-2018 10:42 AM

Thank you so much. i used your code with further adjustment, and it works very well.

May i get one more advice?

another table i try to achieve similar result, and i already make it as simple as possible to build a comparison,

table as below:

Value_1 | Template_1 | Identity_1 | Identity_2 |

510119Z00A | 21438399;510109Z03B | 11 | 1 |

510119Z00A | 21550714;510119Z00A | 11 | 1 |

510119Z00A | 21438399;510109Z03B | 11 | 1 |

510119Z00A | 21550714;510119Z00A | 11 | 1 |

510119Z00A | 21438399;510109Z03B | 11 | 1 |

The code i build as advanced expression in EG project as below:

```
CASE
WHEN Identity_1 = '16' or Identity_2 = '0'
THEN '1'
WHEN find(Template_1,Value_1,';')>0
THEN '1'
ELSE '0'
END
```

i found that FIND statement doesn't work, since it always give result as 0.

anything wrong i used?

Thank you.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

01-18-2018 10:50 AM

xiaoyao026 wrote:

Thank you so much. i used your code with further adjustment, and it works very well.

May i get one more advice?

another table i try to achieve similar result, and i already make it as simple as possible to build a comparison,

table as below:

Value_1 Template_1 Identity_1 Identity_2 510119Z00A 21438399;510109Z03B 11 1 510119Z00A 21550714;510119Z00A 11 1 510119Z00A 21438399;510109Z03B 11 1 510119Z00A 21550714;510119Z00A 11 1 510119Z00A 21438399;510109Z03B 11 1

The code i build as advanced expression in EG project as below:

`CASE WHEN Identity_1 = '16' or Identity_2 = '0' THEN '1' WHEN find(Template_1,Value_1,';')>0 THEN '1' ELSE '0' END`

i found that FIND statement doesn't work, since it always give result as 0.

anything wrong i used?

Thank you.

Maxim 2: Read the Log. You'll find a WARNING.

find() tries to interpret the ';' string as a *modifier*, not a *delimiter*.

Use the findw() function instead, or omit the additional string:

```
data test;
value_1 = '510119Z00A';
template_1 = '21550714;510119Z00A';
x1 = find(template_1,value_1);
x2 = findw(template_1,value_1,';');
run;
```

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

01-18-2018 11:24 PM

Thank you.

When i copy your code into SAS program file with manually typing sample figure and run, it works with result.

but when i use this code directly, i always got result as 0.

anything wrong in the sample data format?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

01-19-2018 01:41 AM

Follow the link I gave you earlier, copy the macro code into your code editor, submit it, and use it as described in the comments. Then post the resulting datastep code in a code window here. Only with real data can we test our codes.

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

01-22-2018 08:58 AM

Thank you very much. i have finally managed to solve this.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

01-18-2018 02:59 AM

So what have you tried so far? Are the variables in "Actual Data" numeric?

Posting input data and required results **as data steps** will make it easier to provide some guidelines or code.