topic Comparing data of two datasets in SAS Enterprise Guide

Comparing data of two datasets

Kanica_V — Fri, 04 Sep 2020 12:45:00 GMT

Hi,

I have one SAS code to write. I would be thankful if anyone of you could help me out!

I have two datasets. Both have two variables - 'Name' and 'Type'. The Name variable has values like - April Provision, August Provision, BP etc. while 'Type' variable represents the datatype of these variables. If 'Type' has value 1 then it is 'Numeric' datatype and if 'Type ' has value 2 then it is 'Char' dataype.
'Name' variable is unique/primary key of the dataset. (The observations in the 'Name' column are same in both the datasets)
Problem - I need to compare these two datasets. So my first dataset is the baseline against which I will be comparing my second dataset. My SAS code should identify those variables for which the datatype has changed. That means it should look for variable names in the first dataset and then compare the datatypes of these variables with those in the second dataset. If there is a change in the datatype (i.e 'Type' column), it should print that variable name, it's old 'Type' (the one in the first dataset) and the new 'Type' (the one in the second dataset). Likewise, the code should check this for all the variables in the first dataset.

The output should also print the variable name along with it's datatype for those variables present in the second dataset but missing in the first dataset.

How can I do this?

Thanks!

Re: Comparing data of two datasets

Tom — Fri, 04 Sep 2020 13:00:28 GMT

Did you try using PROC COMPARE?

Re: Comparing data of two datasets

Kanica_V — Sun, 06 Sep 2020 08:27:50 GMT

Yes. But I'm not getting the desired output.
My datasets are below -
Dataset 1 -
Name Type
August Provison 1
April Provision 1
BP 2
Date 1

Dataset 2 -
Name Type
August Provison 1
April Provision 1
BP 1
Date 1
BP Key 1

Re: Comparing data of two datasets

Kurt_Bremser — Sun, 06 Sep 2020 10:11:11 GMT

See this:

data ds1;
infile datalines dlm="," dsd;
input name :$20. type;
datalines;
August Provison,1
April Provision,1
BP,2
Date,1
;

data ds2;
infile datalines dlm="," dsd;
input name :$20. type;
datalines;
August Provison,1
April Provision,1
BP,1
Date,1
BP Key,1
;

proc sort data=ds1;
by name;
run;

proc sort data=ds2;
by name;
run;

data compare;
merge
  ds1 (in=d1)
  ds2 (in=d2 rename=(type=type2))
;
by name;
nomatch = (d1 and d2 and type ne type2);
new = (d2 and not d1);
run;

Note how I presented input data in data steps with datalines, which makes it easy for us to recreate a dataset with a simple copy/paste and submit; please do so yourself in the future.

Re: Comparing data of two datasets

Kanica_V — Mon, 07 Sep 2020 01:23:30 GMT

Sure will do. This worked. Thanks a lot!