Solved

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

Posted on 2014-01-09
6
318 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now