SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Comparision of two varibles?

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

Comparision of two varibles?

Hello,all:

 

I have some questions:

 

1, I would like to compare two data sets, some variables has the same name but different positions, how to set a flag to find them, and how to ignore.   For example, the variable “ID” has the name of “John” but one is at the left of the column and the other is at the center of the column. 

  1. If sometimes “John” is all in capital “JOHN”, and the other case is in “john”. How to let the system know they refer the same person, and it could be ignore.

With all above, which statement I should be use? Please advice, thanks.


Accepted Solutions
Solution
‎05-17-2017 08:06 AM
Trusted Advisor
Posts: 1,927

Re: Comparision of two varibles?

[ Edited ]

ybz12003 wrote:

Hello,all:

 

I have some questions:

 

1, I would like to compare two data sets, some variables has the same name but different positions, how to set a flag to find them, and how to ignore.   For example, the variable “ID” has the name of “John” but one is at the left of the column and the other is at the center of the column. 

  1. If sometimes “John” is all in capital “JOHN”, and the other case is in “john”. How to let the system know they refer the same person, and it could be ignore.

With all above, which statement I should be use? Please advice, thanks.


First, you have to merge the two datasets, using the MERGE statement of a data step, or using PROC SQL.

 

Capitalization is easy to deal with, just compare the all caps (or all lower case) version of the text, in other words, you can compare UPPER(variable1) to UPPER(variable2). If the text isn't always at the left hand side, you can use the LEFT function to force the text to be at the left hand side. You also want to use the TRIM function to get rid of trailing blanks which could cause the comparison to fail.

 

so, a data step comparison would look something like

 

if trim(left(upper(variable1))) = trim(left(upper(variable2))) then ...

 

View solution in original post


All Replies
Solution
‎05-17-2017 08:06 AM
Trusted Advisor
Posts: 1,927

Re: Comparision of two varibles?

[ Edited ]

ybz12003 wrote:

Hello,all:

 

I have some questions:

 

1, I would like to compare two data sets, some variables has the same name but different positions, how to set a flag to find them, and how to ignore.   For example, the variable “ID” has the name of “John” but one is at the left of the column and the other is at the center of the column. 

  1. If sometimes “John” is all in capital “JOHN”, and the other case is in “john”. How to let the system know they refer the same person, and it could be ignore.

With all above, which statement I should be use? Please advice, thanks.


First, you have to merge the two datasets, using the MERGE statement of a data step, or using PROC SQL.

 

Capitalization is easy to deal with, just compare the all caps (or all lower case) version of the text, in other words, you can compare UPPER(variable1) to UPPER(variable2). If the text isn't always at the left hand side, you can use the LEFT function to force the text to be at the left hand side. You also want to use the TRIM function to get rid of trailing blanks which could cause the comparison to fail.

 

so, a data step comparison would look something like

 

if trim(left(upper(variable1))) = trim(left(upper(variable2))) then ...

 

Regular Contributor
Posts: 228

Re: Comparision of two varibles?

or this suggestion here? Proc compare ignore case specificity

Super Contributor
Posts: 319

Re: Comparision of two varibles?

How to write the command?

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 149 views
  • 2 likes
  • 3 in conversation