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

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

xiaoyao026 — Thu, 18 Jan 2018 06:57:32 GMT

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.

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

Kurt_Bremser — Thu, 18 Jan 2018 07:57:08 GMT

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-data-AKA-generate/ta-p/258712 (as you've already been told before).

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

andreas_lds — Thu, 18 Jan 2018 07:59:45 GMT

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.

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

s_lassen — Thu, 18 Jan 2018 08:57:37 GMT

@Kurt_Bremser:

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;

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

xiaoyao026 — Thu, 18 Jan 2018 15:42:08 GMT

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.

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

Kurt_Bremser — Thu, 18 Jan 2018 15:50:00 GMT

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

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

xiaoyao026 — Fri, 19 Jan 2018 04:24:15 GMT

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?

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

Kurt_Bremser — Fri, 19 Jan 2018 06:41:46 GMT

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.

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

xiaoyao026 — Mon, 22 Jan 2018 13:58:42 GMT

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