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

[Ishaan]

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;

[RW9]

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.

[Ishaan]

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.

[andreas_lds]

---

@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.

[RW9]

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.  

[Patrick]

Would something like below do the job for you?

data have;
  length my_key_var 8;
  array vars_ {200} 8;
  my_key_var=1;
run;

proc sql;
  create table abc like have;
quit;

proc datasets lib=work nolist nowarn;
  modify abc;
    ic create my_key_var_pk=primary key(my_key_var);
  run;
quit;

proc append data=have base=abc;
run;quit;

proc contents data=work.abc;
run;quit;

[BrunoMueller]

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.

[Ronein]

Hello,
Is it primary index?

[BrunoMueller]

Yes the sample code I provided creates a unique index on a column. That is not yet a PK definition.

You would need to add the actual primary key deinition (which would also create the index, if it does not already exist).

proc sql;
alter table new_class add primary key (name);
quit;

[LinusH]

@BrunoMueller 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.

[BrunoMueller]

@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.