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
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 */
%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.
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
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
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.