BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
IgawaKei29
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

10 REPLIES 10
Reeza
Super User

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
Quartz | Level 8

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

Thanks I will try working with this code

IgawaKei29
Quartz | Level 8

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:


01-17-2012 12-56-44 PM.png

Linlin
Lapis Lazuli | Level 10

Hi,

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

IgawaKei29
Quartz | Level 8

That's it.. thank you so much!

IgawaKei29
Quartz | Level 8

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
Super User

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
Quartz | Level 8

Big help, thank you so much

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2371 views
  • 10 likes
  • 4 in conversation