How to fix this error if numberic missing values in given table

[BrahmanandaRao]

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:-

76 dept_id int );
NOTE: Table WORK.BASIC created, with 0 rows and 8 columns.
77
78
79 insert into basic values ( 68319 , 'KAYLING' , 'PRESIDENT' , ,
79 ! 1991-11-18, 6000.00 , 1001 );
NOTE: 1 row was inserted into WORK.BASIC.

80 insert into basic values ( 66928 'BLAZE' , 'MANAGER' , 68319 ,
80 ! 1991-05-01, 2750.00 , 3001 );
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
81 insert into basic values ( 67832 'CLARE' ,'MANAGER' , 68319 ,
81 ! 1991-06-09 , 2550.00 , 1001 );
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
82 insert into basic values ( 65646 'JONAS' ,'MANAGER' , 68319 ,
82 ! 1991-04-02, 2957.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
83 insert into basic values ( 67858 'SCARLET' ,'ANALYST' , 65646 ,
83 ! 1997-04-19, 3100.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
84 insert into basic values ( 69062 'FRANK' ,'ANALYST' , 65646 ,
84 ! 1991-12-03, 3100.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
85 insert into basic values ( 63679 'SANDRINE' ,'CLERK' , 69062
85 ! ,1990-12-18 , 900.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
86 insert into basic values ( 64989 'ADELYN' ,'SALESMAN' , 66928
86 ! ,1991-02-20, 1700.00 , 400.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
87 insert into basic values ( 65271 'WADE' ,'SALESMAN' , 66928
87 ! ,1991-02-22 , 1350.00 , 600.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
88 insert into basic values ( 66564 'MADDEN' ,'SALESMAN' , 66928 ,
88 ! 1991-09-28 , 1350.00 , 1500.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
89 insert into basic values ( 68454 'TUCKER' ,'SALESMAN' , 66928 ,
89 ! 1991-09-08 , 1600.00 , 0.00 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
90 insert into basic values ( 68736 'ADNRES' ,'CLERK' , 67858 ,
90 ! 1997-05-23 , 1200.00 , 2001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
91 insert into basic values ( 69000 'JULIUS' ,'CLERK' , 66928 ,
91 ! 1991-12-03 , 1050.00 , 3001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
92 insert into basic values ( 69324 'MARKER' ,'CLERK' , 67832 ,
92 ! 1992-01-23 , 1400.00 , 1001);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
93
94
95 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 1.82 seconds
cpu time 0.06 seconds

[Shmuel]

I have not checked the whole code, but if you inspect next two lines, you'll find that there are missing commas between the variables:

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	);

[BrahmanandaRao]

I HAVE MISSING VALUES IN MANAGERR AND COMMISSION SO I USE NOT NULL
BUT I GOT ERROR

[Shmuel]

Any null value should be enclosed between commas, otherwise you have to read the data with a data step and use fix positions for each variable value in the row.

[Kurt_Bremser]

So you only want to create a temporary table in WORK with those values?

[Shmuel]

the "emp_id int not null" means the EMP_ID must not be null, but other variables may be null.

[Kurt_Bremser]

The reason for the ERROR is the way you supply the date; you need to use SAS date literals:

proc sql;
create table basic (
  emp_id int not null,
  emp_name char(20),
  job_name char(20),
  manage_id int,
  hire_date date informat=yymmdd10.,
  salary int,
  commission int,
  dept_id int
);
insert into basic values (
  68319,
  'KAYLING',
  'PRESIDENT',
  .,
  '18NOV1991'd,
  6000,
  .,
  1001
);
quit;

To use any other informat for the date, you HAVE to use a DATA step; in fact, the data step code is so simple compared to the SQL that not using a DATA step is nothing but dumb:

data basic;
infile datalines dlm=',' dsd truncover;
input
  emp_id :$5.
  emp_name :$20.
  job_name :$20.
  manage_id :$5.
  hire_date :yymmdd10.
  salary
  commission
  dept_id :$4.
;
format
  hire_date yymmdd10.
  salary commission 12.2
;
datalines;
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
;

[Tom]

You need to have a value for each field, if you don't list the specific fields.  Use period to indicate a missing numeric, use ' ' to indicate an empty string. 

Also use date literals for date values.

So your variable types appear to be #,$,$,#,date,#,#,#

So you could use either of these:

insert into basic values (68319,'KAYLING',' ',.,'18NOV1991'd,6000.00,.,1001) ;
insert into basic (emp_id,emp_name,hire_date,salary,dept_id)
  values (68319,'KAYLING','18NOV1991'd,6000.00,1001) ;

It will be a lot easier to create data using a data step instead of trying to stuff the observations in one by one using SQL VALUES() clause. Then you could leave your date string in YMD order and just use the proper informat to convert those strings into date values. If you use delimited data then you can just leave the "null" values as empty.

data basic;
  infile cards dsd truncover;
  input emp_id emp_name :$20. job_name :$20. manage_id hire_date :yymmdd.
       salary commission dept_id
  ;
  format hire_date yymmdd10. ;
cards;
68319,KAYLING,,,1991-11-18,6000.00,,1001
;