Use header name in the rows

[hs71]

Hi,

I have three columns T1,T2,T3 where T1 contains names, T2 contains Age and T3 contains Gender. Now, I want to add a new columns, lets say T_ONE which will have concatenation of header T1 with name (for instance, T1 - John, T1 - Brad and so on), T_TWO which will have concatenation of header T2 with age (for instance T2 -- 25, T2 - 35 etc.)

In another words, I just want to use my header name in the observations.

Please help.

[PeterClemmensen]

Hi and welcome to the SAS Communities 🙂

I think this is what you want

data have;
input T1 $ T2 T3 $;
datalines;
Alice 10 Female
Tim   30 Male  
Bob   50 Male  
;

data want;
    set have;
    t_one=catx(' - ', vname(T1), T1);
    t_two=catx(' - ', vname(T2), T2);
    t_three=catx(' - ', vname(T3), T3);
run;

[hs71]

Thanks a lot. The output is exactly what I want. But is there another way as well where I do not have to define the column name explicitly?

[PeterClemmensen]

Why don't you want to reference the variables explicitly? Do you have a lot of them?

[hs71]

Yes, I have quite a few in my dataset.

I was having an idea...but dont know how to execute? Can we address the sas to use the header of second column and concatenate it with the name, then third column and so on. Because the sequence of columns in my dataset is constant. Always first column will be T1, second will be T2 and so on.

[Jagadishkatam]

I believe one way is to simply the approach by using the arrays as below, the below code i used with work for character variables, the same need to be repeated for numeric variables.

data have;
input T1 $ T2 T3 $;
datalines;
Alice 10 Female
Tim   30 Male  
Bob   50 Male  
;

data want;
    set have;
    array var1(*) t1 t3;
     array var3(*) $100. T_1 T_3;
     do i = 1 to dim(var3);
     if vtype(var1(i))='C' then var3(i)=catx(' - ', vname(var1(i)), var1(i));
          *if vtype(var2(i))='N' then var3(i)=catx(' - ', vname(var2(i)), var2(i));
          end;
run;

[Jagadishkatam]

Here is a dynamic approach by which we can derive n number of variables as you expected

data have;
input T1 $ T2 T3 $;
datalines;
Alice 10 Female
Tim   30 Male  
Bob   50 Male  
;

proc sql noprint;
/*create macro variables for character and numeric variables*/
 select name into: chars separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE' and type='char' order by type, name;
  select name into: nums separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE' and type='num' order by type, name;
  /*create macro variables for character and numeric variables with names changed i.e to have names like T_1... instead of T1*/
select prxchange('s/(\w[a-z]*)(\d*)/$1_$2/i',1,name)  into: newn separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE' and type='num' order by type, name;
select prxchange('s/(\w[a-z]*)(\d*)/$1_$2/i',1,name)  into: newc separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE' and type='char' order by type, name;
quit;
%put &chars &nums &newn &newc;

data want;
    set have;
    array var1(*) &chars;
    array var2(*) &nums;
    array var3(*) $100. &newn;
     array var4(*) $100. &newc;
     do i = 1 to dim(var1);
     var4(i)=catx(' - ', vname(var1(i)), var1(i));
          end;
     do i = 1 to dim(var2);
     var3(i)=catx(' - ', vname(var2(i)), var2(i));
          end;
run;

[hs71]

Thanks Jag for the solution!!!

[Jagadishkatam]

Good to know, If you are satisfied with the solution and if it actually worked could you mark this solution as accepted so it will help other sas community users.