DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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

 

IdentityValue 1Value 2
2563412562700
2611833644700
   

 

Template Data

IdentityTemplate 1Template 2
234222;33;35;52;912340300
252282;832522400;2522800
252427;82;83;712522800
256331;412560300;2562700
261124;822610800
2631292631800

 

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: 9,548

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

Posted in reply to xiaoyao026

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

View solution in original post


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

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

Posted in reply to xiaoyao026

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: 226

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

Posted in reply to KurtBremser

@KurtBremser:

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

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

Posted in reply to KurtBremser

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_1Template_1Identity_1Identity_2
510119Z00A21438399;510109Z03B111
510119Z00A21550714;510119Z00A111
510119Z00A21438399;510109Z03B111
510119Z00A21550714;510119Z00A111
510119Z00A21438399;510109Z03B111

 

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: 9,548

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

Posted in reply to xiaoyao026

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

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

Posted in reply to KurtBremser

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: 9,548

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

Posted in reply to xiaoyao026

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

Posted in reply to KurtBremser

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

Super Contributor
Posts: 498

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

Posted in reply to xiaoyao026

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 130 views
  • 3 likes
  • 4 in conversation