## Convert Data comparison function from Excel Macro code to SAS Programming Code

Solved
Occasional Contributor
Posts: 18

# Convert Data comparison function from Excel Macro code to SAS Programming Code

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
Super User
Posts: 10,524

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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

All Replies
Solution
‎01-22-2018 08:58 AM
Super User
Posts: 10,524

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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
PROC Star
Posts: 269

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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

``````  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;```
Occasional Contributor
Posts: 18

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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.

Super User
Posts: 10,524

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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.

``````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
Occasional Contributor
Posts: 18

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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?

Super User
Posts: 10,524

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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
Occasional Contributor
Posts: 18

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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

Valued Guide
Posts: 621

## Re: Convert Data comparison function from Excel Macro code to SAS Programming Code

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.

☑ This topic is solved.