BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Raj_C
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

 

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. 

View solution in original post

25 REPLIES 25
RahulG
Barite | Level 11
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.

 

Raj_C
Obsidian | Level 7

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;

Raj_C
Obsidian | Level 7

Kindly find the attched screenshot.


Compare.PNG
Reeza
Super User

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

 

Use proc export to get data to excel. 

Reeza
Super User

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

 

Use proc export to get data to excel. 

RahulG
Barite | Level 11

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.

RahulG
Barite | Level 11

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

Raj_C
Obsidian | Level 7

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

RahulG
Barite | Level 11
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. 

Raj_C
Obsidian | Level 7

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.

RahulG
Barite | Level 11

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.

Raj_C
Obsidian | Level 7

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


assign.PNG
Reeza
Super User

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-Ap...

 

 

RahulG
Barite | Level 11

 

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 25 replies
  • 2333 views
  • 10 likes
  • 4 in conversation