Desktop productivity for business analysts and programmers

How to create primary key while creating table using select * from

Reply
Occasional Contributor
Posts: 15

How to create primary key while creating table using select * from

I am trying to create a table using select * from. Can I create primary key in the same step. 

Is it possible to do it without using alter table or proc datasets or creating table first with primary key and inserting data into it. 

eg:

proc sql;

Create table abc as select * from sashelp.class;

quit;

 

Super User
Super User
Posts: 9,866

Re: How to create primary key while creating table using select * from

From the documentation:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002294522.htm

 

You can apply primary keys when creating a new empty table.  Then you would insert data into that table:

proc sql;
  create table abc (...) constraint primary key(...);
  insert into abc select * from sashelp.class;
quit;

Or you would use the alter table option.

Occasional Contributor
Posts: 15

Re: How to create primary key while creating table using select * from

Thanks. But I am looking for something which I can use while selecting the data.

I have more than 200+ variable in source table. I am not willing to create the table first and than insert the data into it.

 

Valued Guide
Posts: 631

Re: How to create primary key while creating table using select * from


@Ishaan wrote:

Thanks. But I am looking for something which I can use while selecting the data.

I have more than 200+ variable in source table. I am not willing to create the table first and than insert the data into it.

 


Sometimes reading the documentation is time-saving, so have a look at the create table syntax. I don't think that separating create and insert will have a negative impact on run-time.

Super User
Super User
Posts: 9,866

Re: How to create primary key while creating table using select * from

As I said, consult the documentation.  As you will see it is not possible to create table metadata when selecting.  Create the table first, then insert data.  Or create the data then alter the table.  Either works, and has no impact really.  This is how it works in SQL, database or other.  You create a table, set properties, keys, indexes etc.  Then you add data to it.  If that's not to you taste, don't use SQL.  200+ columns in one table is bad design choice.  Databases a RDBMS, meaning they are relational, data separated out into minimal storage, then have the ability to join data.  

SAS Super FREQ
Posts: 831

Re: How to create primary key while creating table using select * from

Yes you can.

 

In the query builder window of SAS Enterprise Guide, go to the Options window.

In the field labeled options you can enter SAS Data Set Options. One of them allows you to create indexes. Use the following text:

 

index=(yourColumn / unique)

here is a sample of the code generated:

 

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_CLASS(index=(name/unique)) AS 
   SELECT t1.Name, 
          t1.Sex, 
          t1.Age, 
          t1.Height, 
          t1.Weight
      FROM SASHELP.CLASS t1;
QUIT;

Please be aware, that if your column does not have unique values an error is generated, and the table will not be created.

Super User
Posts: 5,921

Re: How to create primary key while creating table using select * from

@Bruno_SAS even if unique indexes and PK usually have the same use cases, they are not always interchangeable, like if you wish to create a FK in a related table.
Data never sleeps
SAS Super FREQ
Posts: 831

Re: How to create primary key while creating table using select * from

@LinusH you are right creating the unique index will not create the PK definition.

 

So I guess there is no way around a second step to create the PK definition.

Ask a Question
Discussion stats
  • 7 replies
  • 167 views
  • 0 likes
  • 5 in conversation