CODEWITHSUNDEEP
sasviewteradataproc-sql
Harshit Krishnakumar
Harshit Krishnakumar

Reputation: 3

How to order columns created using Proc SQL in SAS

I am using Proc SQL to create Teradata views. I used Execute (pass through facility) and passed the column names as using variables. But the views which are getting created do not have the columns in the order which was passed into the query. They are getting created at any random order. Is there a way to fix this?

Upvotes: 0

Views: 319

Answers (2)

Dominic Comtois
Dominic Comtois

Reputation: 10401

Not familiar with Teradata per se, more used to working with SAS/DB2, but what if instead of using execute() you would use something like this -- this will create the view on the SAS side (which might not be what you're after, I'm not entirely sure).

proc sql;
 connect to teradata (user=testuser password=testpass);
 create view work.myView as
   select * from connection to teradata
    (select var1, var2, var3 
     from someTable);
quit;

Upvotes: 0

serge_k
serge_k

Reputation: 1772

Using the method described here:

data tmp;
aa = 1;
db = 1;
ac = 1;
bb = 1;
run;

proc sql ;
  select name into :VARLIST separated by ', '
  from dictionary.columns
  where libname = 'WORK'
    and memname = 'TMP' 
  order by name ;
quit ;

proc sql ;
  create table ordered as
  select &VARLIST
  from work.tmp ;
quit ;

Upvotes: 1

Related Questions