topic Re: How to fix this error if numberic missing values in given table in Statistical Procedures

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

BrahmanandaRao — Sat, 26 Sep 2020 08:33:23 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:-

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

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

Shmuel — Sat, 26 Sep 2020 10:08:32 GMT

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

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

BrahmanandaRao — Sat, 26 Sep 2020 10:40:52 GMT

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

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

Shmuel — Sat, 26 Sep 2020 12:22:17 GMT

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.

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

Kurt_Bremser — Sat, 26 Sep 2020 12:23:35 GMT

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

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

Shmuel — Sat, 26 Sep 2020 12:24:36 GMT

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

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

Kurt_Bremser — Sat, 26 Sep 2020 12:41:17 GMT

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
;

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

Tom — Sat, 26 Sep 2020 14:27:25 GMT

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
;