BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xiaoyao026
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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).

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

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).

s_lassen
Meteorite | Level 14

@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;
xiaoyao026
Calcite | Level 5

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.

 

 

Kurt_Bremser
Super User

@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;
xiaoyao026
Calcite | Level 5

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?

Kurt_Bremser
Super User

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.

xiaoyao026
Calcite | Level 5

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

andreas_lds
Jade | Level 19

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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