topic Re: Duplicate columns with a different name in SAS Programming

Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 15:05:24 GMT

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!

Re: Duplicate columns with a different name

art297 — Thu, 27 Apr 2017 15:22:47 GMT

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

Re: Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 15:26:15 GMT

Hello!

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

Thanks

Re: Duplicate columns with a different name

Kurt_Bremser — Thu, 27 Apr 2017 15:27:35 GMT

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;

Re: Duplicate columns with a different name

art297 — Thu, 27 Apr 2017 15:28:16 GMT

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

Re: Duplicate columns with a different name

RW9 — Thu, 27 Apr 2017 15:30:03 GMT

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;

Re: Duplicate columns with a different name

novinosrin — Thu, 27 Apr 2017 15:33:10 GMT

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;

Re: Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 15:43:11 GMT

Hello,

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

Thanks

--Sujith

Re: Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 15:51:56 GMT

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

Re: Duplicate columns with a different name

art297 — Thu, 27 Apr 2017 15:52:13 GMT

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

Re: Duplicate columns with a different name

RW9 — Thu, 27 Apr 2017 16:00:57 GMT

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;

Re: Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 16:04:21 GMT

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;

Re: Duplicate columns with a different name

art297 — Thu, 27 Apr 2017 16:21:01 GMT

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

Re: Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 16:26:59 GMT

It worked!

Thanks a ton!!

Re: Duplicate columns with a different name

Sujithpeta — Thu, 27 Apr 2017 16:27:39 GMT

Thanks for the help guys!