topic Re: How to create primary key while creating table using select * from in SAS Enterprise Guide

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

Ishaan — Mon, 25 Jun 2018 10:12:53 GMT

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;

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

RW9 — Mon, 25 Jun 2018 10:50:18 GMT

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.

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

Ishaan — Mon, 25 Jun 2018 11:14:53 GMT

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.

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

andreas_lds — Mon, 25 Jun 2018 11:32:02 GMT

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

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

RW9 — Mon, 25 Jun 2018 12:08:29 GMT

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.

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

BrunoMueller — Mon, 25 Jun 2018 21:08:33 GMT

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.

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

LinusH — Tue, 26 Jun 2018 06:09:06 GMT

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

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

BrunoMueller — Tue, 26 Jun 2018 06:19:15 GMT

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

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

Ronein — Wed, 30 Mar 2022 06:00:52 GMT

Hello,
Is it primary index?

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

BrunoMueller — Wed, 30 Mar 2022 06:29:08 GMT

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;

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

Patrick — Wed, 30 Mar 2022 09:09:21 GMT

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;