topic Re: Proc compare in SAS Programming

Proc compare

draroda — Thu, 31 Aug 2017 14:26:41 GMT

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

Re: Proc compare

draroda — Thu, 31 Aug 2017 14:27:41 GMT

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

Please consider it as single lib

Re: Proc compare

Reeza — Thu, 31 Aug 2017 14:38:17 GMT

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;

Re: Proc compare

draroda — Thu, 31 Aug 2017 14:52:20 GMT

Hi,

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

Re: Proc compare

Reeza — Thu, 31 Aug 2017 14:55:35 GMT

Post your full code.

Re: Proc compare

draroda — Thu, 31 Aug 2017 15:40:24 GMT

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

run;

%end;

proc append base=master data=new;run;

%MEND;
%CHECK;

Re: Proc compare

Reeza — Thu, 31 Aug 2017 15:42:54 GMT

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

Re: Proc compare

draroda — Fri, 01 Sep 2017 03:49:02 GMT

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.

Re: Proc compare

Reeza — Fri, 01 Sep 2017 04:15:28 GMT

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;

Re: Proc compare

draroda — Sun, 03 Sep 2017 08:10:49 GMT

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