topic Re: how to create primary key and how add primary key already existing table in Statistical Procedures

how to create primary key and how add primary key already existing table

BrahmanandaRao — Fri, 25 Sep 2020 05:58:44 GMT

proc sql;
create table hr_emp (	emp_id int,emp_name varchar(20),job_name varchar(20),
manager_id int primary key , hire_date  date, salary int, commission int primary key);

insert into hr_emp values(	68319,  'KAYLING', 'PRESIDENT',    1991-11-18 , 6000.00  ,     1001);

quit;

insert data :

emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001

Re: how to create primary key and how add primary key already existing table

Kurt_Bremser — Fri, 25 Sep 2020 06:45:46 GMT

Well, this worked. What is your question?

Re: how to create primary key and how add primary key already existing table

SASKiwi — Fri, 25 Sep 2020 06:46:41 GMT

What database are you trying to create this table in? You will need to use SQL passthru and the SQL dialect of the database to create tables and to change the schema of existing tables.

Re: how to create primary key and how add primary key already existing table

BrahmanandaRao — Fri, 25 Sep 2020 07:19:44 GMT

i created hr_emp table but i missed primary key constraint how to add primary key already existing table

in the data missing values are there

below are the error

Re: how to create primary key and how add primary key already existing table

Kurt_Bremser — Fri, 25 Sep 2020 07:43:21 GMT

You can have only one primary key variable in a table. Remove the second definition.

It's tough that we tave to tell so often, but

Do no post logs as pictures!
Post them by copy/pasting log text into a </> window!
Always post the complete log when you get an ERROR or WARNING, or you can't make complete sense of it!

Re: how to create primary key and how add primary key already existing table

BrahmanandaRao — Sat, 26 Sep 2020 06:48:30 GMT

proc sql;
create table basic (emp_id int not null ,
                    emp_name varchar(20),
																				job_name	varchar(20),
																				manage_id int not null,
																				hire_date	date	,
																				salary				int 	,
																				commission int not null,
																				dept_id				int );


insert into basic values (	68319 , 'KAYLING'  , 'PRESIDENT' ,     ,        1991-11-18,  6000.00  ,              1001	);
insert into basic values ( 66928  'BLAZE'    , 'MANAGER'    ,     68319 ,    1991-05-01,  2750.00    ,            3001	);
insert into basic values ( 67832  'CLARE'     ,'MANAGER'    ,    68319 ,    1991-06-09 , 2550.00    ,            1001	);
insert into basic values ( 65646  'JONAS'     ,'MANAGER'    ,    68319  ,   1991-04-02,  2957.00    ,            2001);
insert into basic values ( 67858  'SCARLET'   ,'ANALYST'    ,     65646   ,  1997-04-19,  3100.00    ,            2001);
insert into basic values ( 69062  'FRANK'     ,'ANALYST'    ,    65646    , 1991-12-03,  3100.00    ,            2001);
insert into basic values ( 63679  'SANDRINE'  ,'CLERK'      ,    69062     ,1990-12-18 ,  900.00    ,            2001);
insert into basic values ( 64989  'ADELYN'    ,'SALESMAN'    ,    66928     ,1991-02-20,  1700.00    ,  400.00 ,  3001);
insert into basic values ( 65271  'WADE'      ,'SALESMAN'    ,    66928     ,1991-02-22 , 1350.00    ,  600.00 ,  3001);
insert into basic values ( 66564  'MADDEN'    ,'SALESMAN'    ,    66928   ,  1991-09-28 , 1350.00    , 1500.00 ,  3001);
insert into basic values ( 68454  'TUCKER'    ,'SALESMAN'    ,    66928   ,  1991-09-08 , 1600.00    ,    0.00    3001);
insert into basic values ( 68736  'ADNRES'    ,'CLERK'       ,    67858    , 1997-05-23 , 1200.00    ,            2001);
insert into basic values ( 69000  'JULIUS'    ,'CLERK'       ,    66928    , 1991-12-03 , 1050.00    ,            3001);
insert into basic values ( 69324  'MARKER'    ,'CLERK'       ,    67832    , 1992-01-23 , 1400.00    ,            1001);


quit;

error

51 insert into basic values ( 68319 , 'KAYLING' , 'PRESIDENT' , ,
51 ! 1991-11-18, 6000.00 , 1001 );
NOTE: 1 row was inserted into WORK.BASIC.

52 insert into basic values ( 66928 'BLAZE' , 'MANAGER' , 68319 ,
52 ! 1991-05-01, 2750.00 , 3001 );
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
53 insert into basic values ( 67832 'CLARE' ,'MANAGER' , 68319 ,
53 ! 1991-06-09 , 2550.00 , 1001 );
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
54 insert into basic values ( 65646 'JONAS' ,'MANAGER' , 68319 ,
54 ! 1991-04-02, 2957.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
55 insert into basic values ( 67858 'SCARLET' ,'ANALYST' , 65646 ,
55 ! 1997-04-19, 3100.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
56 insert into basic values ( 69062 'FRANK' ,'ANALYST' , 65646 ,
56 ! 1991-12-03, 3100.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
57 insert into basic values ( 63679 'SANDRINE' ,'CLERK' , 69062
57 ! ,1990-12-18 , 900.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
58 insert into basic values ( 64989 'ADELYN' ,'SALESMAN' , 66928
58 ! ,1991-02-20, 1700.00 , 400.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
59 insert into basic values ( 65271 'WADE' ,'SALESMAN' , 66928
59 ! ,1991-02-22 , 1350.00 , 600.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
60 insert into basic values ( 66564 'MADDEN' ,'SALESMAN' , 66928 ,
60 ! 1991-09-28 , 1350.00 , 1500.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
61 insert into basic values ( 68454 'TUCKER' ,'SALESMAN' , 66928 ,
61 ! 1991-09-08 , 1600.00 , 0.00 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
62 insert into basic values ( 68736 'ADNRES' ,'CLERK' , 67858 ,
62 ! 1997-05-23 , 1200.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
63 insert into basic values ( 69000 'JULIUS' ,'CLERK' , 66928 ,
63 ! 1991-12-03 , 1050.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
64 insert into basic values ( 69324 'MARKER' ,'CLERK' , 67832 ,
64 ! 1992-01-23 , 1400.00 , 1001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
65
66
67 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.57 seconds
cpu time 0.18 seconds

Re: how to create primary key and how add primary key already existing table

Kurt_Bremser — Sat, 26 Sep 2020 07:11:47 GMT

This is a different problem than that in your initial question.

To create a dataset from code, use a DATA step with DATALINES. It is much easier.

Re: how to create primary key and how add primary key already existing table

BrahmanandaRao — Sat, 26 Sep 2020 07:14:02 GMT

i want to create table through proc sql

Re: how to create primary key and how add primary key already existing table

Kurt_Bremser — Sat, 26 Sep 2020 07:26:55 GMT

Maxim 14.

@BrahmanandaRao wrote:
i want to create table through proc sql

Re: how to create primary key and how add primary key already existing table

BrahmanandaRao — Sat, 26 Sep 2020 07:37:39 GMT

what Maxim14

Re: how to create primary key and how add primary key already existing table

Kurt_Bremser — Sat, 26 Sep 2020 09:46:28 GMT

@BrahmanandaRao wrote:
what Maxim14

See the first link in my footnotes.