topic Re: Proc Compare or Rename in SAS Procedures

Proc Compare or Rename

IgawaKei29 — Mon, 16 Jan 2012 21:31:50 GMT

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

Re: Proc Compare or Rename

Reeza — Mon, 16 Jan 2012 21:48:14 GMT

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;

Proc Compare or Rename

IgawaKei29 — Mon, 16 Jan 2012 22:09:06 GMT

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;

Re: Proc Compare or Rename

Linlin — Mon, 16 Jan 2012 23:04:37 GMT

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

Proc Compare or Rename

IgawaKei29 — Tue, 17 Jan 2012 12:40:34 GMT

Thanks I will try working with this code

Re: Proc Compare or Rename

IgawaKei29 — Tue, 17 Jan 2012 17:57:35 GMT

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:

Re: Proc Compare or Rename

Linlin — Tue, 17 Jan 2012 18:05:05 GMT

Hi,

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

Re: Proc Compare or Rename

IgawaKei29 — Tue, 17 Jan 2012 18:11:16 GMT

That's it.. thank you so much!

Re: Proc Compare or Rename

IgawaKei29 — Fri, 20 Jan 2012 11:30:46 GMT

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?

Re: Proc Compare or Rename

Ksharp — Fri, 20 Jan 2012 12:09:17 GMT

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

Re: Proc Compare or Rename

IgawaKei29 — Fri, 20 Jan 2012 13:18:55 GMT

Big help, thank you so much