BookmarkSubscribeRSS Feed
hs71
Fluorite | Level 6

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.

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6

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
Tourmaline | Level 20

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

hs71
Fluorite | Level 6

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
Amethyst | Level 16

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;
Thanks,
Jag
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
hs71
Fluorite | Level 6

Thanks Jag for the solution!!!

Jagadishkatam
Amethyst | Level 16
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.
Thanks,
Jag

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 833 views
  • 3 likes
  • 3 in conversation