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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
draroda
Fluorite | Level 6

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

 

Please consider it as single lib

Reeza
Super User

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
Fluorite | Level 6

Hi,

 

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

Reeza
Super User

Post your full code. 

draroda
Fluorite | Level 6

%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
Super User

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

draroda
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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

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
  • 9 replies
  • 1954 views
  • 0 likes
  • 2 in conversation