topic Re: Use header name in the rows in New SAS User

Use header name in the rows

hs71 — Thu, 05 Sep 2019 09:17:50 GMT

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.

Re: Use header name in the rows

PeterClemmensen — Thu, 05 Sep 2019 09:22:27 GMT

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;

Re: Use header name in the rows

hs71 — Thu, 05 Sep 2019 09:29:49 GMT

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?

Re: Use header name in the rows

PeterClemmensen — Thu, 05 Sep 2019 09:35:15 GMT

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

Re: Use header name in the rows

hs71 — Thu, 05 Sep 2019 09:44:38 GMT

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.

Re: Use header name in the rows

Jagadishkatam — Thu, 05 Sep 2019 11:15:32 GMT

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;

Re: Use header name in the rows

Jagadishkatam — Thu, 05 Sep 2019 12:28:01 GMT

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;

Re: Use header name in the rows

hs71 — Fri, 06 Sep 2019 06:20:47 GMT

Thanks Jag for the solution!!!

Re: Use header name in the rows

Jagadishkatam — Fri, 06 Sep 2019 07:10:54 GMT

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.