topic Re: Variable comparison in two different datasets in SAS Programming

Variable comparison in two different datasets

Akshaya_1397 — Fri, 23 Jun 2023 06:46:17 GMT

Hi all,

I have two completely different datasets.

Dataset1 -clm has variables counterparty,id,name

Dataset2-gsl has variable splr_name,relid,phn

Now I need to compare the counterparty name in clm and splr_name in gsl in

New dataset and report the not matching ones.

Can anyone please help me on this.

Can I simply use proc compare or is there any other way .

Re: Variable comparison in two different datasets

HenryKobus — Fri, 23 Jun 2023 07:46:42 GMT

proc compare check on observation level meaning check osb 1 from clm is equal to obs 1 from gsl. I unterstand your task is to catch observations which have no match in the opposite table. So my solution is:

proc sort data = clm;
	by counterparty;
run;

proc sort data = gsl;
	by splr_name;
run;

data 	clm_only (rename =(joinvar = counterparty ) drop = relid phn)
	gsl_only (rename =(joinvar = splr_name ) drop = id name);
merge   clm (in =inclm rename =(counterparty = joinvar))
	gsl (in =ingsl rename =(splr_name = joinvar));
by joinvar;
if ingsl eq 0 then output clm_only;
if inclm eq 0 then output gsl_only;
run;

Re: Variable comparison in two different datasets

ballardw — Fri, 23 Jun 2023 10:05:02 GMT

@Akshaya_1397 wrote:

Hi all,

I have two completely different datasets.

Dataset1 -clm has variables counterparty,id,name

Dataset2-gsl has variable splr_name,relid,phn

Now I need to compare the counterparty name in clm and splr_name in gsl in

New dataset and report the not matching ones.

Can anyone please help me on this.

Can I simply use proc compare or is there any other way .

It is not clear what you mean by "compare" and "not matching". Proc Compare is a observation by observation comparison by default. So is likely not the tool unless the data sets are expected to have the same number (or at least pretty close) of observations with similar values.

Do want just properties of the variables such as length, format and label?

Or values that appear in one data set but not the other? This code will create two data sets with one observation of each value that appears in one data set but not the other. I think the names of the sets explain the contents. Obviously untested as we do not have your data sets.

proc sql;
   create table in_clm_not_gsl as
   select distinct counterparty as value from clm
   except
   select distinct splr_name as value from gsl
   ;
   create table in_gsl_not_clm as
   select distinct splr_name as value from gsl
   except
   select distinct counterparty as value from clm
   ;
quit;

Warning: These are equal comparisons. So if your variable is character case differences will be reported as "BOB" is not the same as "Bob".