Accepted Solutions
@purpleclothlady It might be worth to investigate what Proc Compare could do for you.
Below just some sample code to get you started. Proc Compare got a lot of options that allow you to get what you want - and besides of an out= option you can also always use ods to capture results and store it in a table for further processing and shape the data exactly as you want them.
data new;
input id ds $ ta $ f $ ccc $ z y $;
datalines;
101 ds2 la cah tx 15 s
;
data old;
input id ds $ o_ta $ o_f $ o_ccc $ o_z o_y $;;
datalines;
101 ds2 al cah tx 1 s
;
proc compare base=new comp=old;
by id;
var ds ta f ccc;
with ds o_ta o_f o_ccc;
run;
hi @Tom and all:
I got the answer of the question, hopefully the code explained the goal. have a great day
/*create data sets*/
/*keepvar is required by doc*/
%let keepvar= ta f ccc;
%let keepvarold= o_ta o_f o_ccc;
data new;
input id ds $ ta $ f $ ccc $ z y $;
datalines;
101 ds2 la cah tx 15 s
run;
/*keep the required var.*/
data newf;
set new (keep=id ds &keepvar.);
run;
data old;
input id ds $ o_ta $ o_f $ o_ccc $ o_z o_y $;;
datalines;
101 ds2 al cah tx 1 s
run;
data oldf;
set old (keep=id ds &keepvarold.);
run;
/*add prefix to both new and old variables for compare the difference*/
data names;
set sashelp.vcolumn;
where libname='WORK' and memname='NEWF' and name not in ("ID","DS");
new_name = catt("NEW_", _n_);
keep new_name name;
run;
proc sql noprint;
select catx("=", name, new_name) into :rename_list separated by " "
from names;
quit;
proc datasets lib=work nodetails nolist;
modify newf;
rename &rename_list;
run;quit;
data oldnames;
set sashelp.vcolumn;
where libname='WORK' and memname='OLDF' and name not in ("ID","DS");
old_name = catt("OLD_", _n_);
keep OLD_name name;
run;
proc sql noprint;
select catx("=", name, old_name) into :oldrename_list separated by " "
from oldnames;
quit;
proc datasets lib=work nodetails nolist;
modify oldf;
rename &oldrename_list;
run;quit;
proc sort data=oldf;by id ds;run;
proc sort data=newf;by id ds;run;
/*Merge oldf and newf to find out WHICH VARIABLE is different by using array*/
data adif ;
length _cat _catf $15.;
merge oldf(in = old )
newf(in = new );
by id ds;
if new and not old then _cat ="New Record";
if old and not new then _cat ="Deleted Record";
if new and old then _cat ="Both";
array vcold{*} old_:;
array vcnew{*} new_:;
array ckc {*} ckc1 -ckc3;
do i = 1 to dim(vcold);
if _cat ="Both" and vcnew{i} ne vcold{i} then do;
_catf ="Revised";
ckc{i} = 1;
end;
end;
drop i old_:;
run;
@purpleclothlady It might be worth to investigate what Proc Compare could do for you.
Below just some sample code to get you started. Proc Compare got a lot of options that allow you to get what you want - and besides of an out= option you can also always use ods to capture results and store it in a table for further processing and shape the data exactly as you want them.
data new;
input id ds $ ta $ f $ ccc $ z y $;
datalines;
101 ds2 la cah tx 15 s
;
data old;
input id ds $ o_ta $ o_f $ o_ccc $ o_z o_y $;;
datalines;
101 ds2 al cah tx 1 s
;
proc compare base=new comp=old;
by id;
var ds ta f ccc;
with ds o_ta o_f o_ccc;
run;
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.
-
4098 views
-
3 likes
-
-
