CODEWITHSUNDEEP
sas
Alex Lekkakos
Alex Lekkakos

Reputation: 29

SAS - Rename Variables depending on another dataset

I'm using SAS and i have a dataset with vars named a_17, a_18, a_19,..., a_27. I also have a dataset like this 

id  name
17  SDDF COCONUT HG
18  SILK ALMD_CCNT BLEND HG
19  SILK ALMOND 2CT HG
20  SILK ALMOND HG
21  SILK ALMOND LT HG
22  SILK CASHEW HG
23  SILK COCONUT HG
24  SILK QT ORGANIC
25  SILK SOY 2CT HG
26  SILK SOY HG
27  SILK SOY LT HG

Is there anyway to rename my vars and replace numbers(id) with names with a macro and not manually?

Example:

Thanks in advance and excuse my poor English!

Upvotes: 2

Views: 218

Answers (3)

user667489
user667489

Reputation: 9569

Here's a call execute solution:

/* required variable names */
data var_names;
input @1 id  @3 name $30.;  
datalines;
17  SDDF COCONUT HG
18  SILK ALMD_CCNT BLEND HG
19  SILK ALMOND 2CT HG
20  SILK ALMOND HG
21  SILK ALMOND LT HG
22  SILK CASHEW HG
23  SILK COCONUT HG
24  SILK QT ORGANIC
25  SILK SOY 2CT HG
26  SILK SOY HG
27  SILK SOY LT HG
;
run;

/* create dummy dataset with vars a_17 - a_27 */
data have;
array current_vars{*} a_17-a_27;
run;

data _null_;
    set var_names end = eof;
    if _n_ = 1 then     call execute('proc datasets lib= work; modify have; rename ');
                        call execute(cats('a_',id,'=','a_',tranwrd(trim(name),' ','_')));
    if eof then         call execute('; run; quit;');
run;

Upvotes: 1

Longfish
Longfish

Reputation: 7602

My answer assumes that all your variables start with 'a_'. If that's the case then you can build up a rename statement using your lookup dataset, then apply that in proc datasets.

/* required variable names */
data var_names;
input @1 id  @3 name $30.;
datalines;
17  SDDF COCONUT HG
18  SILK ALMD_CCNT BLEND HG
19  SILK ALMOND 2CT HG
20  SILK ALMOND HG
21  SILK ALMOND LT HG
22  SILK CASHEW HG
23  SILK COCONUT HG
24  SILK QT ORGANIC
25  SILK SOY 2CT HG
26  SILK SOY HG
27  SILK SOY LT HG
;
run;

/* build up rename statement, replacing spaces with underscores */
proc sql noprint;
select cats('a_',id,'=','a_',translate(trim(name),'_',' ')) 
        into :new_vars
        separated by ' '
from var_names;
quit;

%put &new_vars.;

/* cerate dummy dataset with vars a_17 - a_27 */
data have;
input a_17-a_27;
datalines;
1 2 3 4 5 6 7 8 9 10 11
;
run;

/* rename variables */
proc datasets lib=work nodetails nolist;
modify have;
rename &new_vars.;
quit;

Upvotes: 3

Puneet Tripathi
Puneet Tripathi

Reputation: 422

Below code should help. i created a dataset which is similar to your dataset which needs to rename the cols and 2nd "b" for looking up the values from.

data a;
length a_17 a_18 a_19 8;
run;

data b;
input id  name $;
datalines;
17  SDDF
18  SILK
19  SILK21
;

proc sql noprint;
select id , name  into :id separated by ':', :name separated by ':' from b;
quit;

%put &id,&name;

%macro abc;

data a;
    set a;
    %let i =  1;
    %let id1 = %scan(&id,&i,":");

    %do %while( %str(&id1) ne );
        %let strng = a_%scan(&name,&i,":");
        rename a_&id1 = &strng;
        %let i =  &i+1;
        %let id1 = %scan(&id,&i,":");
    %end;
run;

%mend;

%abc;

HTH

Upvotes: 0

Related Questions