topic Re: Merge 2 SAS datasets with same format name in 2 catalogs in New SAS User

Merge 2 SAS datasets with same format name in 2 catalogs

Thanyada — Fri, 15 Nov 2019 17:50:23 GMT

Some people gave me 2 SAS datasets and 2 SAS catalog files

I would like to merge those datasets

But the problem is each SAS catalog file containing the same name format.

libname data1 "<location>";

libname data2 "<location>";

options fmtsearch = (data1 data2);

proc sort data = data1;

by V1;

run;

proc sort data = data2;

by V1;

run;

data temp1;

merge data1.dataset1 data2.dataset2;

by V1;

run;

How can I deal with the same format names in 2 SAS catalog files.

Thank you

Re: Merge 2 SAS datasets with same format name in 2 catalogs

Tom — Fri, 15 Nov 2019 17:53:55 GMT

Are the formats in fact different? Or are they just copies of the same format?

Re: Merge 2 SAS datasets with same format name in 2 catalogs

Thanyada — Fri, 15 Nov 2019 17:58:35 GMT

The formats are different

Re: Merge 2 SAS datasets with same format name in 2 catalogs

Tom — Fri, 15 Nov 2019 18:12:43 GMT

So you need to change the name of one of them. Probably best to not mess with the original and instead make a new catalog.

Let's make two format catalogs that both have the same format name in them.

proc format lib=work.formatsA;
value x 1='Version A';
run;
proc format lib=work.formatsB;
value x 1='Version B';
run;

Now we can use PROC CATALOG to copy those into a new catalog and rename them.

proc catalog cat=work.formats_all;
  copy in=work.formatsA out=work.formats_all;
   select x /et=format;
  run;
  change x=xA (et=format);
  run;
  copy in=work.formatsB out=work.formats_all;
   select x /et=format;
  run;
  change x=xb (et=format);
  run;
quit;

Note that the entry type of character formats is FORMATC instead of FORMAT. Also remember that format names cannot end in a digit.

Now you need to change the format attached the variables to match the new names.

So let's assume that the variables A in DATASET1 and B in DATASET2 are both using the format X. So just add a FORMAT statement to the data step that merges them to attach the renamed formats instead.

data temp1;  
  merge data1.dataset1 data2.dataset2;
  by V1;
  format A XA.  B XB. ;
run;

If the variable names are the same (In addition to the format names that are attached to them) then you probably need to rename one or both of the variables. For example if the variable is named X in both datasets you probably want something like this instead.

data temp1;  
  merge data1.dataset1(rename=(X=XA))
        data2.dataset2(rename=(X=XB))
  ;
  by V1;
  format XA XA.  XB XB. ;
run;

Re: Merge 2 SAS datasets with same format name in 2 catalogs

Thanyada — Fri, 15 Nov 2019 18:38:19 GMT

Thank you so much for you assistance. It is very helpful.