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
Solved

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

Posted on 2014-01-09
6
356 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
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

IT certifications are a concrete representation of continual learning on the part of the candidate.  Continual learning is necessary for the long term success of an IT professional, but are IT certifications the right path for you?
Giving access to ESXi shell console is always an issue for IT departments to other Teams, or Projects. We need to find a way so that teams can use ESXTOP for their POCs, or tests without giving them the access to ESXi host shell console with a root …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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