BookmarkSubscribeRSS Feed
BrahmanandaRao
Lapis Lazuli | Level 10

 

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

 

10 REPLIES 10
SASKiwi
PROC Star

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.

BrahmanandaRao
Lapis Lazuli | Level 10

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

 

Anandkvn_1-1601018363977.png

 

 

 

 

 

 

 

Anandkvn_0-1601018331522.png

 

Kurt_Bremser
Super User

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!
BrahmanandaRao
Lapis Lazuli | Level 10
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

 

BrahmanandaRao
Lapis Lazuli | Level 10
i want to create table through proc sql

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is ANOVA?

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.

Discussion stats
  • 10 replies
  • 1190 views
  • 0 likes
  • 3 in conversation