?
Solved

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

Posted on 2014-01-09
6
Medium Priority
?
392 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:acbxyz
ID: 39768564
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
 

Author Comment

by:morinia
ID: 39768600
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
 
LVL 10

Expert Comment

by:acbxyz
ID: 39768637
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
Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

 

Author Comment

by:morinia
ID: 39768726
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
 
LVL 14

Accepted Solution

by:
Aloysius Low earned 2000 total points
ID: 39769887
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
 

Author Comment

by:morinia
ID: 39769918
Lowaloysius,



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

Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This month, Experts Exchange’s free Course of the Month is focused on CompTIA IT Fundamentals.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question