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
Well, this worked. What is your question?
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.
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
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
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
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.
@BrahmanandaRao wrote:
what Maxim14
See the first link in my footnotes.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.