BookmarkSubscribeRSS Feed
Aditi24
Obsidian | Level 7

Hi,

I have two sas libraries which I want to compare:
Assume
Library1: input
Library2: output

I need to put a validation where I need to compare these two libraries and check whether they have same no of datasets. If the number is same I have to copy datasets from output into a third library.

If the datasets are not same I have to get all the "uncommon" datasets in a variable and print it the log.

Please suggest a way for such comparison.

5 REPLIES 5
andreas_lds
Jade | Level 19

Interesting task. Seems a bit strange to assume that the same number of members implies that the same members are in both libraries. The following steps extract the member names from sashelp.vtable and print the uncommon members in the log.

 

      proc sql noprint;
         create table work.input as
            select MemName
               from sashelp.vtable
                  where libname = 'INPUT'
         ;
         create table work.output as
            select  MemName
               from sashelp.vtable
                  where libname = 'OUTPUT'
         ;
      quit;

      data work.compared;
         merge work.input(in= ininput) work.output(in= inOutput);
         by MemName;

         if sum(of in:) = 1;

         put MemName;
      run; 

 

ballardw
Super User

You say that you want to copy data sets to a third library when the number of datasets matches in two different libraries. The number of data sets would not be a guarantee that the sets are the same as the sets could have different names. Even if the names of the sets are the same they could be different based on numbers or names of variables or simply number of records in the same-named set.

 

You may want to be more precise describing the actual purpose of this process.

cpagrawal
Fluorite | Level 6
%macro _lets_compare(lib1=,lib2=, lib3=);

 /*count the datasets and check if number of datasets are equal*/
   proc sql ; 
		select count(*) into : cnt_lib1_dtst
		from dictionary.tables 
		where libname = upcase("&lib1");
   quit; 
   proc sql ;
		select count(*) into : cnt_lib2_dtst
		from dictionary.tables 
		where libname = upcase("&lib2");
   quit; 
   
   %if &cnt_lib1_dtst ne &cnt_lib2_dtst %then %do ; 
		%put %str("the count of datasets not matched");
		title "datasets in &lib1 but not in &lib2"; 
		proc sql ; 	
			select memname
			from dictionary.tables 
			where libname = upcase("&lib1")
			except 
			select memname
			from dictionary.tables 
			where libname = upcase("&lib2");
		quit; 
		
		title "datasets in &lib2 but not in &lib1"; 
		proc sql ; 	
			select memname
			from dictionary.tables 
			where libname = upcase("&lib2")
			except 
			select memname
			from dictionary.tables 
			where libname = upcase("&lib1");
		quit; 
   %end;
   %else %do ; 
		%put %str("the count of datasets matched");
   %end; 
   
   /* copy the datasets into third library */ 
   
   proc copy in = &lib2 out= &lib3 ;
   run;
   
%mend _lets_compare ; 
Aditi24
Obsidian | Level 7
@cpagrawal: I need to print the unmatched datasets as well.Please help.
cpagrawal
Fluorite | Level 6

@Aditi24Can you please write your requirement again please ? 
if you can give the following a try may be it will help you. 
https://www.mwsug.org/proceedings/2011/appdev/MWSUG-2011-AD09.pdf

 

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
  • 5 replies
  • 3323 views
  • 2 likes
  • 4 in conversation