compare two datasets

[Raj_C]

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

[RahulG]

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

/*   Code to find compare column list - START */
proc contents data=have2 out=contents2 noprint;
run;

%macro where_cond;
%do i=1 %to &key_col_cnt;
%if &i < &key_col_cnt %then 
    %do;
		%let col=%sysfunc(scan( &key_col,&i));
		 "&col" ","
	%end;	
	%else 
	%do;
		%let col=%sysfunc(scan( &key_col,&i));
		 "&col" ", "
	%end;
	
%end;
%mend;



proc sql noprint;
select name into :compare_col separated by ' '
from contents2
where name not in (%where_cond);
quit;
%put COMPARE COLUMN = &compare_col ;
/*   Code to find compare column list - END */


%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;

You can find the code to list down compare column between comment

/*   Code to find compare column list - START */

/*   Code to find compare column list - END */

[Raj_C]

Thank you so much Rahul.

[Raj_C]

Hi Rahul,

Can you please explain where key_col_cnt is defined?

%if &i < &key_col_cnt %then

[RahulG]

%macro where_cond;
%do i=1 %to &key_col_cnt;
		%let col=%sysfunc(scan( &key_col,&i));
		 "&col" ","
%end;
%mend;

Lets make it simple. Ignore that.

[Raj_C]

I am getting same warning Rahul.

WARNING: Apparent symbolic reference KEY_COL_CNT not resolved.

[Raj_C]

sorry for the inconvenience, it's working.

[SJN]

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,

SJ

[RahulG]

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

[SJN]

 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.

[RahulG]

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.