[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-01-09
6
Medium Priority
?
411 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
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.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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