Proc Compare or Rename

[IgawaKei29]

I am not exactly sure how to go about doing this and was hoping someone could help.  I have numerous datasets that I want to compare, but the problem is that they have different column names.  Is there a way in a data step to rename part of a coloumn if it begins with a certain letter or set of letters?

So if the coloum looked like this Base_period

Could I tell SAS to look for the letters Base and once found to rename to Curr, so it would now read Curr_period

[Reeza]

Does the WITH statement help you at all in proc compare? Then you can avoid renaming to the same name?

data class2;

    set sashelp.class;

    drop height age;

    if sex='F' then height2=height-1;

        else height2=height;

    if sex='M' then age2=age/2; else age2=age;

run;

proc compare base=sashelp.class compare=class2;

var age height;

with age2 height2;

run;

[IgawaKei29]

The issue is that in the data sets I am looking at there are a number of combination Base could be.  That's why I want to replace Base with Curr.  I wouldn't want to try and list out all 100 of so columns that I am trying to compare, just rename what I can.

Here is my current code:

proc compare brief

                     base=data.Set1

                     compare=data.Set2

                     method=percent criterion=2

                     out=data.test1;

run;

[Linlin · Accepted solution]

Hi,

You can use this code to rename your variables.

data have;

input base_id base_age base_height;

cards;

1 20 6.0

2 25 5.9

;

run;

proc sql noprint ;

select catx('=',name,cats('curr_',scan(name,-1,'_'))) into : base separated by ' '

  from dictionary.columns

   where libname='WORK' and memname='HAVE' and name like 'base~_%' escape '~';

quit;

%put &base ;

proc datasets library=work nolist;

modify have;

  rename &base ;

quit;

proc print; run;

                                                                    curr_

                     Obs    curr_id    curr_age    height

                        1            1          20             6.0

                        2            2          25             5.9

Linlin

[IgawaKei29]

Thanks I will try working with this code

[IgawaKei29]

OK I think I almost have it, however when I run the program I am getting an error that &base is not defined.  Here is my updated program:

[Linlin]

Hi,

Libname and memname must be capital letters. please change memname='cp_???' to 'CP_???'.

[IgawaKei29]

That's it.. thank you so much!

[IgawaKei29]

One more follow up question.  I am running into the problem when my field is named : base_pct_cnt  when I run the code I end up with curr_base_cnt.  How would I alter my code to account for 3 or 4 _ seperators?

[Ksharp]

How about it:

data have;
input base_id base_age base_pct_cnt ;
cards;
1 20 6.0
2 25 5.9
;
run;

proc sql noprint ;
select catx('=',name,cats('curr',substr(name,findc(name,'_')))) into : base separated by ' '
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and name like 'base~_%' escape '~';
quit;

%put &base ;

 

proc datasets library=work nolist;
modify have;
  rename &base ;
quit;

Ksharp

[IgawaKei29]

Big help, thank you so much