DATA Step, Macro, Functions and more

Duplicate columns with a different name

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Duplicate columns with a different name

Hello,

 

I need help with this small thing.

I've 25 columns which I want to duplicate each one to make 50 columns by adding "columnname_Base"  to the duplicate column, is there way to do automated instead of doing manually?

 

Ex: Have dataset Columns:  X    Y    Z  

      Want dataset Columns:  X    Y    Z    X_Base    Y_Base   Z_Base

 

Thanks!


Accepted Solutions
Solution
‎04-27-2017 12:26 PM
PROC Star
Posts: 7,364

Re: Duplicate columns with a different name

[ Edited ]

The libname and memname, when using dictionary.columns, must be in UPPERCASE

 

Thus, you would have to use:

 

proc sql noprint;
  select catt(name,'_base=',name,';')
    into : creates separated by ' '
      from dictionary.columns
        where libname= "E" and
              memname='COMB_FILE_VTE_F'
  ;
quit;

data E.Comb_File_VTE_F1;
  set E.Comb_File_VTE_F;
  &creates.
run;

 

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
PROC Star
Posts: 7,364

Re: Duplicate columns with a different name

You just want to create those variables, or create and also copy their values? And are they all character, numeric or a mixture of both?

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 43

Re: Duplicate columns with a different name

Hello! 

I want to create and copy values, its a mixture of char and num variable types.

Thanks

PROC Star
Posts: 7,364

Re: Duplicate columns with a different name

Here is one way:

 

data have;
  input x y $ z;
  cards;
1 a 3
2 b 1
;

proc sql noprint;
  select catt(name,'_base=',name,';')
    into : creates separated by ' '
      from dictionary.columns
        where libname='WORK' and
              memname='HAVE'
  ;
quit;

data want;
  set have;
  &creates.
run;

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 43

Re: Duplicate columns with a different name

Hello,

If it's possible can you explain what is the code doing? It would be a great learning.

Thanks

--Sujith

PROC Star
Posts: 7,364

Re: Duplicate columns with a different name

If you were asking about my code, it's fairly simple. Dictionary.columns contains all of the metadata for a file, one of which is NAME, namely the variable names. Thus the call to proc sql

proc sql noprint;
  select catt(name,'_base=',name,';')
    into : creates separated by ' '
      from dictionary.columns
        where libname='WORK' and
              memname='HAVE'
  ;
quit;

simply creates a macro variable using the catt function to create, in this case, the following string (as if you were manually typing it):

 

x_base=x; y_base=y; z_base=z;

 

thus when you then submit:

 

data want;
  set have;
  &creates.
run;

SAS will actually run:

data want;
  set have;
x_base=x;
y_base=y;
z_base=z;
run; 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 43

Re: Duplicate columns with a different name

I used your same code. In place of libname is used "E" instead of "WORK", for "HAVE" i used my file name "Comb_File_VTE_F".

Looks like I made a mistake, when i ran the code I got the log output as "No rows selected". Can you point my mistake?

Thanks

 

proc sql noprint;
  select catt(name,'_base=',name,';')
    into : creates separated by ' '
      from dictionary.columns
        where libname= "E" and
              memname='Comb_File_VTE_F'
  ;
quit;

data E.Comb_File_VTE_F1;
  set E.Comb_File_VTE_F;
  &creates.
run;

 

Solution
‎04-27-2017 12:26 PM
PROC Star
Posts: 7,364

Re: Duplicate columns with a different name

[ Edited ]

The libname and memname, when using dictionary.columns, must be in UPPERCASE

 

Thus, you would have to use:

 

proc sql noprint;
  select catt(name,'_base=',name,';')
    into : creates separated by ' '
      from dictionary.columns
        where libname= "E" and
              memname='COMB_FILE_VTE_F'
  ;
quit;

data E.Comb_File_VTE_F1;
  set E.Comb_File_VTE_F;
  &creates.
run;

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 43

Re: Duplicate columns with a different name

It worked!

Thanks a ton!!

Super User
Posts: 6,963

Re: Duplicate columns with a different name

You can extract the variable specifications from dictionary.columns (in SQL) or sashelp.vcolumn (data step).

Then you can create a data step that adds variables, and sets attributes and values:

proc sql;
create table columns as
select *
from dictionary.columns
where libname = 'SASHELP' and memname = 'CLASS';
quit;

data _null_;
set columns end=done;
if _n_ = 1 then call execute("data class; set sashelp.class; ");
call execute("attrib " !! strip(name) !! "_base length=");
if type = "char" then call execute("$");
call execute(strip(put(length,best.)));
if format ne " " then call execute(" format=" !! strip(format));
call execute("; " !! strip(name) !! "_base=" !! strip(name) !! ";");
if done then call execute("run;");
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,413

Re: Duplicate columns with a different name

Well, you can:

data have;
  x=1; y=2; z=5;
run;

data _null_;
  set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;
  if _n_=1 then call execute('data want; set have;');
  call execute(cats('base_',name,'=',name,';'));
  if last then call execute('run;');
run;

I would really advise against going down this route however.  You lose the inbuilt functionality of lists and arrays which will make you life so much easier.  For instance:

data want;
  set have;
  array vals{3} x y z;
  array base_{3};
  do i=1 to dim(vals);
    base_{i}=vals{i};
  end;
run;

This will create Base_1, Base_2, etc.   You can always now refer to that range in code as array base_{*}; and loop over it.  Makes your coding simpler.  Same with your variables x y z.  If you make these a list, then you can simply all your code:

data have;
  value1=1; value2=2; value3=5;
run;
data want;
  set have;
  array values value:;
  array base{3};
  do i=1 to dim(values);
    base{i}=values{i};
  end;
run;

 

Contributor
Posts: 43

Re: Duplicate columns with a different name

Array method is not working because all the variables aren't of the same data type. Can you explain the first method, I'm new to SAS and don't know what's doing.

 

Thanks

Super User
Super User
Posts: 7,413

Re: Duplicate columns with a different name

[ Edited ]

Much like @art297's solution, I use the SAS metadata columns, in a datastep rather than a macro variable, so on the first row, the intial data line is created, then for each other row the copy is done, then on the final is is finished.  If you run it you will see it creates the code:

data want;

  set have;

  base_x=x;

...

run;

PROC Star
Posts: 172

Re: Duplicate columns with a different name

[ Edited ]

Do they all share the same datatype?, if yes, would something like this help:

 

proc sql;

select catx('_',name,'Base') into :name separated by ' '

from dictionary.columns

where libname='YOUR_LIBREF' and memname='YOUR_DATASET_NAME';

quit;

 

data want;

set thave;

retain &name ' ';

RUN;

 

 

Contributor
Posts: 43

Re: Duplicate columns with a different name

Thanks for the help guys!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 244 views
  • 0 likes
  • 5 in conversation