Proc compare

[draroda]

Hi all,

I have two Libraries.Production and QC.

Number of dataset in both libraries vary as per project.

I want to run proc compare on both libs and create common dataset which reflects the status as per value of sysinfo -automatic macro variable.

I have created below.

%MACRO CHECK;
proc sql;
select count(distinct(memname)) into :cnt from dictionary.columns
where libname="prod";
QUIT;
%LET cnt=&cnt;
PROC SQL;
select distinct(memname) into: name1 -:name&cnt
from dictionary.columns
where libname="qc";
quit;
%do i=1 %to &cnt;
proc compare base=prod.&&name&cnt compare=qc.&&name&cnt;
run;
%let CompareSysinfo=&sysinfo;

DATA new;
length item $20;
sysinfo=&CompareSysinfo.;
item="&&name&cnt";
output;

run;

%end;

%MEND;
%CHECK;

All proc compare run successfully.Dataset new created to hold value of sysinfo in dataset.

Now, i want to append all dataset , so that i can pass message for sysinfo and generate report.

Can you please help on same.

Regards,

Rajesh

[draroda]

just quick update, as i have passed different lib in proc sql.

Please consider it as single lib

[Reeza]

Add a PROC APPEND statement at the end of hte macro to append the results. You should also drop the table so you don't accidently use it again.

proc append base=master data=new;
run;

proc sql; 
drop table new;
quit;

[draroda]

Hi,

I tried it but it is not holding all records.Only last record appear in append dataset.

[Reeza]

Post your full code. 

[draroda]

%MACRO CHECK;
proc sql;
select count(distinct(memname)) into :cnt from dictionary.columns
where libname="prod";
QUIT;
%LET cnt=&cnt;
PROC SQL;
select distinct(memname) into: name1 -:name&cnt
from dictionary.columns
where libname="qc";
quit;
%do i=1 %to &cnt;
proc compare base=prod.&&name&cnt compare=qc.&&name&cnt;
run;
%let CompareSysinfo=&sysinfo;

DATA new;
length item $20;
sysinfo=&CompareSysinfo.;
item="&&name&cnt";
output;

run;

%end;

proc append base=master data=new;run;

%MEND;
%CHECK;

[Reeza]

You put it outside of your loop, it needs to be inside the loop. 

[draroda]

Hi,

In put it inside the loop and now it write the records equal to number of datasets in library but the observation is repeating from last dataset only.

[Reeza · Accepted solution]

Run your full program with the options below and post the log. You didn't include the SQL code I suggested either which prevents mistakes from occurring. 

Options symbolgen mprint mlogic;

[draroda]

Hi Reeza,

Thanks for suggesting to turn on debg options.

I have done it and replaced &cnt with &i after do loop.

Rest of the program remain same.

Regards,

Rajesh