DATA Step, Macro, Functions and more

Proc compare

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Proc compare

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


Accepted Solutions
Solution
‎09-03-2017 04:11 AM
Super User
Posts: 22,820

Re: Proc compare

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;

View solution in original post


All Replies
Contributor
Posts: 47

Re: Proc compare

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

 

Please consider it as single lib

Super User
Posts: 22,820

Re: Proc compare

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;
Contributor
Posts: 47

Re: Proc compare

Hi,

 

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

Super User
Posts: 22,820

Re: Proc compare

Post your full code. 

Contributor
Posts: 47

Re: Proc compare

%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;

Super User
Posts: 22,820

Re: Proc compare

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

Contributor
Posts: 47

Re: Proc compare

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.

Solution
‎09-03-2017 04:11 AM
Super User
Posts: 22,820

Re: Proc compare

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;
Contributor
Posts: 47

Re: Proc compare

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 287 views
  • 0 likes
  • 2 in conversation