BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sujithpeta
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

14 REPLIES 14
art297
Opal | Level 21

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

 

Sujithpeta
Quartz | Level 8

Hello! 

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

Thanks

art297
Opal | Level 21

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

 

Sujithpeta
Quartz | Level 8

Hello,

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

Thanks

--Sujith

art297
Opal | Level 21

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

 

Sujithpeta
Quartz | Level 8

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;

 

art297
Opal | Level 21

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

 

Sujithpeta
Quartz | Level 8

It worked!

Thanks a ton!!

Kurt_Bremser
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

Sujithpeta
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

novinosrin
Tourmaline | Level 20

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;

 

 

Sujithpeta
Quartz | Level 8

Thanks for the help guys!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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