topic Re: sas proc compare in SAS Programming

sas proc compare

mauri0623 — Thu, 24 Jan 2019 19:42:27 GMT

I have a bunch of sas program in one folder and a bunch in another folder, for example folder a located at /saswork/imatemp/a and folder b at /saswork0/output/b. I want a proc compare that compares the contents of a and b and tell me what is in a that is not in b and visa versa. Thank you for all the help that you can provide.

Re: sas proc compare

Reeza — Thu, 24 Jan 2019 19:52:06 GMT

See this program here:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0js70lrkxo6uvn1fl4a5aafnlgt.htm&docsetVersion=9.4&locale=en

1. Run it once for one folder and capture the data into a data set

2. Run it again for your second folder

3. Run PROC COMPARE on output from 1 vs 2.

This assumes you're looking for differences such as folder a has Program ABSC.sas while folder b does not.

Another quick way is as follows, assuming windows:

   Filename filelist pipe "dir /b /s c:\temp\*.sas"; 
                                                                                   
   Data fileA;                                        
     Infile filelist truncover;
     Input filename $100.;
     Put filename=;
   Run;

Replace the path C:\temp with your own path.

Re: sas proc compare

SuryaKiran — Thu, 24 Jan 2019 19:56:56 GMT

Hello,

Run this macro to find the list of .sas file in a folder or sub-folder for path 'a' and path 'b'. After creating the two datasets you can use proc compare or proc sql (except) to find the file that are missing.

Note: This is for Unix/Linux, if your running it in Windows then you might need to change the '/' to '\' in the code.

%macro Files_List(dsn, /* Output Dataset name. <libref>.<dataset name> e.g: Work.Files        */
			      dir, /* Directory name(Unix/Linux only). For windows need to change slashes */
			      ext  /* File extension  for search without period. i.e: sas or xlsx or txt  */  
				);   
   %if %sysfunc(exist(&dsn))=0 %then %do;

	/* Create a table */
		proc sql;
		create table &dsn  
				( File_Name char(1000) format=$1000. 
				);
		quit;

   %end;
 
  %local filrf rc did memcnt name i;                                                                                                    
                                                                                                                                        
  /* Assigns a fileref to the directory and opens the directory */                                                           
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                               
  %let did=%sysfunc(dopen(&filrf));                                                                                                     
  /* Make sure directory can be open */                                                                                                 
  %if &did eq 0 %then %do;                                                                                                              
   %put Directory &dir cannot be open or does not exist;                                                                                
   %return;                                                                                                                             
  %end;                                                                                                                                 
                                                                                                                                        
   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  
                                                                                                                                        
     /* Retrieve name of each file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));                                                                                               
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                   
     /* If condition is true print the full name to the log        */                                                                   
      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do; 
     /* Insert directory path name into table */
		proc sql;
		insert into &dsn
			set File_Name= "&dir/&name";
		quit;
      %end;                                                                                                                             
     /* If directory name call macro again */                                                                                           
      %else %if %qscan(&name,2,.) = %then %do;                                                                                          
        %Files_List(&dsn,&dir/%unquote(&name),&ext)                                                                                               
      %end;                                                                                                                             
                                                                                                                                        
   %end;   

                                                                                                                                        
  /* Closes the directory and clear the fileref */                                                                                      
  %let rc=%sysfunc(dclose(&did));                                                                                                       
  %let rc=%sysfunc(filename(filrf));                                                                                                    
                                                                                                                                        
%mend Files_List; 

/* Invoke the macro, example */
/*%Files_List(work.test,/home/kiran,sas);*/

Re: sas proc compare

mauri0623 — Mon, 28 Jan 2019 13:35:44 GMT

Thank you. Your suggestion help me resolve the problem.

Re: sas proc compare

mauri0623 — Mon, 28 Jan 2019 13:36:38 GMT

Thank you. Great technique. This solved my problem.

Re: sas proc compare

ChrisNZ — Tue, 29 Jan 2019 03:04:26 GMT

@SuryaKiran SAS really needs to provide functions to create data sets and add data so this kind of macros can run without generating any code.