topic Re: compare two datasets in SAS Programming

compare two datasets

Raj_C — Mon, 29 Aug 2016 04:31:02 GMT

Hi SAS Users,

SAS dataset 1:

data have1;

input id var $ sex $;

datalines;

10 bala m

10 raju m

11 chak f

11 sudh m

12 keer f

;

run;

SAS dataset 2:

data have2;

input id var $ sex $ sal;

datalines;

10 balaa m 200

11 raju f 100

12 chak m 400

11 sudh m 500

12 keer m 350

;

run;

we need to compare have1 SAS dataset with have2 SAS dataset and output should contain unequal data (if any new variable is recorded that also we need to report in the output for e.g. sal is the extra variable here).

Kind Regards,

Raju

Re: compare two datasets

RahulG — Mon, 29 Aug 2016 05:09:24 GMT

data have1;
input id var $ sex $;
datalines;
10 bala m
10 raju m
11 chak f
11 sudh m
12 keer f
;
run;
 
 

 
data have2;
input id var $ sex $ sal ;
datalines;
10 balaa m 200
11 raju f 100
12 chak m 400
11 sudh m 500
12 keer m 350
;
run;
proc sort data=have1 out=have1_sorted;
by id;run;
proc sort data=have2 out=have2_sorted;;
by id;run;

proc compare base=have1_sorted compare=have2_sorted;
run;

If input dataset are sorted then no need to add proc sort code.

It will generate a report that can be saved as pdf file.

Do check the proc compare it has much more options to use. I have given example of the most simplest.

Re: compare two datasets

Raj_C — Mon, 29 Aug 2016 05:50:53 GMT

Hi Rahul,

Thank you for your response.

Even I tried this one, but how to report it in Excel.

for the following code the output should look like the attached screenshot in Excelsheet.

data have1;
input id var $ sex $;
datalines;
10 bala m
11 chak f
12 keer f
;
run;

data have2;
input id var $ sex $;
datalines;
10 balaa m
11 sudh m
12 chak m
;
run;

Re: compare two datasets

Raj_C — Mon, 29 Aug 2016 05:51:59 GMT

Kindly find the attched screenshot.

Re: compare two datasets

Reeza — Mon, 29 Aug 2016 06:09:59 GMT

Use a SQL merge with a case statement to check for your conditions.

Use proc export to get data to excel.

Re: compare two datasets

Reeza — Mon, 29 Aug 2016 06:09:59 GMT

Use a SQL merge with a case statement to check for your conditions.

Use proc export to get data to excel.

Re: compare two datasets

RahulG — Mon, 29 Aug 2016 06:19:46 GMT


proc sort data=have1 out=have1_sorted;
by id;run;
proc sort data=have2 out=have2_sorted;;
by id;run;

proc transpose data=have1 out=have1_trans (rename= (_name_=col_name));
by id;
var var sex ;
run;

proc transpose data=have2 out=have2_trans(rename= (_name_=col_name)) ;
by id;
var var sex sal;
run;

proc sql;
create table compare_table
as
select coalesce(a.id,b.id) as ID,coalesce(a.col_name,b.col_name) as var
, a.col1 as old_val
,b.col1 as new_val
,case when a.col1 <> b.col1 and a.id= b.id then 'Modified'
      when a.col1 <> b.col1 and a.id=. then 'Added'
      when a.col1 <> b.col1 and b.id=. then 'Deleted' end as remark 

from have1_trans a
full join have2_trans b
on a.id = b.id and a.col_name = b.col_name;
quit;

You can then export the final table to xls using proc export.

Re: compare two datasets

RahulG — Mon, 29 Aug 2016 06:49:44 GMT

For testing purpose, I have kept all the rows. You can filter the rows from output dataset.

Re: compare two datasets

Raj_C — Mon, 29 Aug 2016 23:24:44 GMT

Hi Rahul & SAS Users,

data have1;

input id var $ record sex $ sal fold $ rate;

datalines;

10 bala 1 m 200 v1 5

10 bala 2 m 300 v1 4

10 bala 3 f 200 a2 6

11 sri 1 m 204 d2 2

11 sri 2 f 408 d4 3

12 koti 1 m 490 v3 4

12 koti 3 f 300 b3 5

12 koti 5 m 203 n4 3

;

run;

data have2;

input id var $ record sex $ sal fold $ rate;

datalines;

10 bala 1 f 220 v1 5

10 bala 2 m 300 v1 4

10 bala 3 m 230 a2 7

11 sri 1 m 204 v2 2

11 sri 2 m 468 d4 3

12 koti 1 f 491 n3 4

12 koti 3 m 300 b3 5

12 koti 5 f 203 n4 3

12 koti 6 m 213 n4 3

;

run;

now by keeping id, var and record as key coloumns (for e.g. (10 bala 1) record of have1 SAS dataset should compare with (10 bala 1) record of have2 SAS dataset) we need to compare rest of all variables and the output should be look like the screenshot attached which shown earlier.

if any new record comes (for e.g. (12 koti) is having two records in have1 SAS dataset but in have2 (12 koti 6) added so in this case it should show like new record added.)

Thank you Rahul for your quick response and patience.

Kind Regards,

Raju

Re: compare two datasets

RahulG — Tue, 30 Aug 2016 03:51:07 GMT

data have1;
input id var $ record sex $ sal fold $ rate;
datalines;
10 bala 1 m 200 v1 5
10 bala 2 m 300 v1 4
10 bala 3 f 200 a2 6
11 sri 1 m 204 d2 2
11 sri 2 f 408 d4 3
12 koti 1 m 490 v3 4
12 koti 3 f 300 b3 5
12 koti 5 m 203 n4 3
;
run;
 
data have2;
input id var $ record sex $ sal fold $ rate;
datalines;
10 bala 1 f 220 v1 5
10 bala 2 m 300 v1 4
10 bala 3 m 230 a2 7
11 sri 1 m 204 v2 2
11 sri 2 m 468 d4 3
12 koti 1 f 491 n3 4
12 koti 3 m 300 b3 5
12 koti 5 f 203 n4 3
12 koti 6 m 213 n4 3
;
run;

%let key_col=id var  record ;
%let compare_col=sex sal fold rate;

%let key_col_cnt=%sysfunc(countw(&key_col));
%macro select_col;
%do i=1 %to &key_col_cnt;
%let col=%sysfunc(scan( &key_col,&i));
coalesce(a.&col,b.&col) as &col,
%end;
%mend;



%macro join_cond;
%do i=1 %to &key_col_cnt;
%let col=%sysfunc(scan( &key_col,&i));
 a.&col =b.&col and 
%end;
%mend;


proc sort data=have1 out=have1_sorted;
by &key_col ;run;
proc sort data=have2 out=have2_sorted;;
by &key_col ;run;

proc transpose data=have1 out=have1_trans (rename= (_name_=col_name));
by &key_col ;
var  &compare_col ;
run;

proc transpose data=have2 out=have2_trans(rename= (_name_=col_name)) ;
by &key_col ;
var &compare_col;
run;

proc sql;
create table compare_table
as
select %select_col
coalesce(a.col_name,b.col_name) as variable_name
,strip(a.col1) as old_val
,strip(b.col1) as new_val
,case when strip(a.col1) <> strip(b.col1) and a.id= b.id and a.record =b.record then 'Modified'
      when strip(a.col1) <> strip(b.col1) and a.id=. then 'Added'
      when strip(a.col1) <> strip(b.col1) and b.id=. then 'Deleted' end as remark 

from have1_trans a
full join have2_trans b
on %join_cond
 a.col_name = b.col_name;
quit;

Automated the above code using two macro variable key_col and compare_col.

User need to supply value to these two macro variables and proc sql changes would be handled itself.

%let key_col=id var  record ;
%let compare_col=sex sal fold rate;

Here there is an assumption key_col and compare_col columns should be present both in have1 and have2.

Re: compare two datasets

Raj_C — Tue, 30 Aug 2016 04:07:45 GMT

You are awesome Rahul.

as you said user will supply the key coloums for key_col macro but can we a code like to take rest all coloums as compare_col.

Re: compare two datasets

RahulG — Tue, 30 Aug 2016 04:20:33 GMT

Surely, you can take it as an excerise yourself. List all the columns using proc dataset and remove key_col. The left would be compare_col list.

Re: compare two datasets

Raj_C — Tue, 30 Aug 2016 04:49:02 GMT

Hi Rahul,

Thank you for giving me a chance to learn.

I have tried the following code and identified the other coloumns. now how to give all these in compare_col macro.

proc contents data=have2 out=contents2 noprint;
run;

proc sort data=contents2;
by varnum;
where varnum > 3;
run;

Kind Regards,
Raju

Re: compare two datasets

Reeza — Tue, 30 Aug 2016 04:55:16 GMT

Please see the recently update macro example section to find the solution to your problem.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/tac-p/292711#M1604

Re: compare two datasets

RahulG — Tue, 30 Aug 2016 05:33:43 GMT

proc contents data=have2 out=contents2 noprint;
run;

proc sql noprint;
select name into :compare_col separated by ' '
from contents2
where varnum>3;
quit;
%put &compare_col ;

This is similar to what you have written below. But it can fail if key column lie after varnum >3

Do refer to @Reeza post.

If you think you got answer to your problem. Please mark it as solution.

Re: compare two datasets

SJN — Tue, 30 Aug 2016 12:29:22 GMT

Hi Rahul,

When I was going through the code I got a doubt.

%macro join_cond;
%do i=1 %to &key_col_cnt;
%let col=%sysfunc(scan( &key_col,&i));
 a.&col =b.&col and 
%end;
%mend;

Here in Line4 . How SAS consider this "AND" here.

a.&col =b.&col and

Can you explain this why "And" is here and how it works Even in the

coalesce(a.&col,b.&col) as &col,

Select_Col macro.

Regards,

Re: compare two datasets

RahulG — Tue, 30 Aug 2016 11:25:46 GMT

I required joining condition which is highlighted below, so and keyword was required.

 a.&col =b.&col and

from have1_trans a
full join have2_trans b
on

a.id = b.id and

a.var=b.var and

a.record=b.record and

a.col_name = b.col_name;

For select I need following

coalesce(a.&col,b.&col) as &col,

coalesce(a.id,b.id) as ID

,coalesce(a.var,b.var) as var

,coalesce(a.record,b.record) as record

Re: compare two datasets

SJN — Tue, 30 Aug 2016 12:32:26 GMT

 a.&col =b.&col and

once these variables are resolved, SAS consider as text and use that in join clause.

When I run the macro alone why its not working. Correct me if I'm wrong.

Re: compare two datasets

RahulG — Tue, 30 Aug 2016 17:07:14 GMT

If I run this code what would be the output

a.id = b.id and 
a.var=b.var and
a.record = b.record

It would throw the error. The above piece of code do not make any sense. But if it used in the join condition, it works well.

I hope I am able to answer you.

Re: compare two datasets

Raj_C — Sat, 03 Sep 2016 11:55:37 GMT

Hi Rahul,

I had refer Reeza's post and I used following code

%let key_col=id var record;

%macro rename;
%let word_cnt=%sysfunc(countw(&key_col));
%do i = 1 %to &word_cnt;
%let temp=%qscan(%bquote(&key_col),&i);
&temp = _&temp
%end;
%mend rename;

data new;
set have2(rename=(%unquote(%rename)));
run;

now I am getting key variables starting with _ (like _id _var _record) but now how to assign other variables to compare_col.

Since I am new to macros requesting you to kindly help.

Kind Regards,

Raju