- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is it primary index?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.