Comparing Dataset dynamically between two library

[_el_doredo]

Hello Experts,

Hope you are all doing good

I need to compare the datasets. I am having 5 datasets, Dataset names may vary depend upon the project. In two library the datasets are saved. I need to compare the dataset between those two library.

In Temp library, I am having 7 files( File count won't change between library)

CT_Sales
Payment_1
Payment_2
Payment_3
758_Transaction
CT758_2_Store
Product

In Temp1 Library, I am 7 files

CT_Sales
Payment_1
Payment_2
Payment_3
758_Transaction
CT758_2_Store
Product

As we can compare these files easily by using PROC COMPARE. But, i want to do it using Macro(Dynamically) so that when the file name changes i don't need to update in the code.

Before comparing i need to do some changes in the dataset.

1.  Some time we will have multiple dataset which has different observation but same variable name. So, If i am having multiple dataset means i need to append those dataset and create a new dataset
2. In my dataset, the original name is(Sales,Payment,Transaction,Store,Product) But I am having some prefix to it and it wont be the same for all dataset. So i need to remove those before comparing. I tried Compress function but, I am not able to achieve the expected result

Once We modify our dataset as mentioned above then I need to compare it dynamically using macro function.

I know it's a lot to ask for. But, I am learning so it would be helpful for my learning

Thanks in advance

[Reeza]

Before you make a macro, the general rule of thumb is working code. Do you currently have working code?

[fja]

Hello!

Why not having a macro that takes the name of a dataset as a parameter, does all the magic that is needed before you are ready to issue the proc compare ... and finally commits the proc compare?

Querying the constitutents of the two libraries and looping over them would be a second step. But all this requires, of course and as Reeza has already mentioned, that you have working code readily available. Is that the case?

Cheers

fja

[Tom]

So you want something that can make some of the decisions for you?   You mentioned two of the those areas where you will need to make decisions,  which datasets can be combined and which variables should be renamed.

In general that means you will need to generate code (what you called dynamic code).  A macro might be one way to make the code dynamic.

But first you will need to figure out what code you need to generate.  And how to gather the information you will need to decide how to generate it.

Here is one method you could use to compare datasets to see if they contain the same set of variables.  Basically use PROC TRANSPOSE to create one observation per dataset with the variables to store all of the variable names.  THen you can use BY group processing to create groups of datasets with the same set of variables.

proc sql noprint;
create table varnames as
  select libname,memname,upcase(name) as uname 
  from dictionary.columns
  where libname='WORK'
  order by libname,memname,uname
;
quit;

proc transpose data=varnames out=memnames(drop=_name_) prefix=var ;
  by libname memname;
  var uname;
run;

proc sort data=memnames ;
  by var: ;
run;

data memnames;
   set memnames ;
   by var: var1;
   group + first.var1 ;
run;

Here is an example result:

NOTE: You mentioned the COMPRESS() function.  That will not be of much value here as that is just used to remove characters from strings.  To remove prefixes you need to use something like SUBSTR() or perhaps TRANWRD() or TRANSTRN() function.

[_el_doredo]

Thank you all for replying. I am on some other priority work. Once I am done with those I will respond to this. 

thanks a lot