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
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;
just quick update, as i have passed different lib in proc sql.
Please consider it as single lib
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;
Hi,
I tried it but it is not holding all records.Only last record appear in append dataset.
Post your full code.
%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;
You put it outside of your loop, it needs to be inside the loop.
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.
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.