Generating a column between each of the columns in a table - SAS

Experts,

I have  table that had 10 columns.  I may have to add a column indicator between each column.  Is there an efficient way to do this?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

acbxyzCommented:
In mysql at least you can use alter table tablename add column newcolumnname ... after oldcolumnname

With microsoft sql server it works at least using the gui, never modified database structure with this dbms.

Which database software are you working with?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
I am using Proc Sql in SAS, so I would think it is mysql.

I know how to add a column by just adding it in a
Proc Sql;
 Create table B as
   Select columna
           ,   column a1  (new colulmn)
           ,   column b
           ,   column b1  (new column)
From Table A;
quit;

I was hoping to find a more efficient way if possible.
0
acbxyzCommented:
That's not modifying a table, its creating a new one.

Have a look at the code on this site:
http://de.saswiki.org/wiki/PROC_SQL#Hinzuf.C3.BCgen.2C_ver.C3.A4ndern_oder_l.C3.B6schen_von_Variablen
The text is german (didn't find english/international version) but the sql code is important.


*It's SAS, not mysql. I didn't read it in your question title first, bad line breaking :-(
mySQL is another DBMS, sas seems to have/be its own sql server.
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

moriniaAdvanced Analytics AnalystAuthor Commented:
Thanks,

I am sorry I said modifying, I can create a new table I was just hoping there was a more efficient way through array processing or something similar to loop around and add the column after each exisiting column without "hardcoding" it in a select statement.
0
Aloysius LowCommented:
there are a few ways to do this dynamically, but they all involve the same starting point - to query the list of columns available from the table:
proc sql;
  select name from sashelp.vcolumn where libname = "your library name; upper case" and memname = "your table name; upper case";
quit;

once you know the names of the columns, you can work on them in the few different ways:
1. using a call execute statement to loop through the table of column names extracted - this is more elegant but harder to code, understand and debug.
2. storing the names into macro variables and loop through the various macro variables - this is more tedious, but easier to understand though not exactly easier to code
3. crafting your select statement containing the column names and the new column names and storing into a macro variable and use it subsequently - this is easier to code, easier to digest, but note that the generated code cannot be more than 65k characters

as an example for #3:
data x;
x=1;y=2;output;
run;

proc sql;
      select trim(left(name)) || ", " || trim(left(name)) || " as " || trim(left(name)) || "_new" into :columns separated by ", "
      from sashelp.vcolumn
      where libname = "WORK" and memname = "X";

      create table y as
      select &columns
      from x;
quit;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moriniaAdvanced Analytics AnalystAuthor Commented:
Lowaloysius,



I wii try tomorrow when I get back to office.  I like the challenge of getting this to work.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Statistical Analysis System (SAS)

From novice to tech pro — start learning today.