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
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 );
So you only want to create a temporary table in WORK with those values?
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
;
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
;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.