topic insert values in proc sql in SAS Procedures

insert values in proc sql

BrahmanandaRao — Sun, 13 Oct 2019 06:38:50 GMT

proc sql;
create table employeesdata
(employee_id         num (3),
first_name           char(15) ,
last_name            char(18), 	
email                char(18) ,
phone_number         num(18)    ,
hire_date            date	,	
job_id               num(9)   ,  		
salary               num(15)  , 	
commission_pct       num(8)	,	
manager_id     	     num(8),	
department_id        num(8));

insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567 ,17-06-2003,	'adpres'	,24000, 0, 0 ,90);

quit;

WARNING: The quoted string currently being processed has become more than 262 characters
long. You might have unbalanced quotation marks.
769 last_name char(18),
770 email char(18) ,
771 phone_number num(18) ,
772 hire_date date ,
773 job_id num(9) ,
774 salary num(15) ,
762 insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,'515.123.4567'
762! ,17-06-2003, 'adpres' ,24000, 0, 0 ,90);
--------------------
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future
SAS release. Inserting white space between a quoted string and the
succeeding identifier is recommended.

Re: insert values in proc sql

Kurt_Bremser — Sun, 13 Oct 2019 07:10:05 GMT

You most probably had unbalanced quotes further up in the code. Restart the SAS session to clean that up.

Re: insert values in proc sql

BrahmanandaRao — Sun, 13 Oct 2019 07:27:09 GMT

Hi sir

i restart the session and run again but still i got error like below

NOTE: Table WORK.EMPLOYEESDATA created, with 0 rows and 11 columns.
14
15 insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567
15 ! ,17-06-2003, 'adpres' ,24000, 0, 0 ,90);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
ERROR: Value 10 of VALUES clause 1 does not match the data type of the corresponding
column in the object-item list (in the SELECT clause).
16
17 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.59 seconds
cpu time 0.09 seconds

RROR: Value 10 of VALUES clause 1 does not match the data type of the corresponding
column in the object-item list (in the SELECT clause).
16
17 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.59 seconds
cpu time 0.09 seconds

Re: insert values in proc sql

Kurt_Bremser — Sun, 13 Oct 2019 08:32:04 GMT

515.123.4567

is not a valid number. Numbers can only have one decimal period.

Since, from your previous post, this column is a phone number, I strongly suggest to store it as character.

17-06-2003

is not a valid SAS date value. To supply that date literal correctly, use

'17jun2003'd

Re: insert values in proc sql

Patrick — Sun, 13 Oct 2019 08:37:02 GMT

@BrahmanandaRao

That's because the values you're trying to insert are invalid for the data types in your source table

This is what you're using

  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567 ,'17-06-2003',  'adpres'  ,24000, 0, 0 ,90);

The values which are invalid:

515.123.4567 - This must be a string which SAS can interpret as a numerical value. The dot is the decimal separator and you can only have one of these.I assume you're using the dot as thousand separator. If so then the value you need to pass is: 5151234567

17-06-2003 - You need to use a SAS date value. One way to go: '17JUN2003'd

'adpres' - You defined column job_id as num(9) so you can't use a string here.

Not throwing an error but still to be considered.

- Unlike for most databases length for a numerical SAS data type doesn't mean the number of digits stored but how much bytes SAS uses to store a number. You would define this with a LENGTH statement. Providing a definition via NUM(n) appears to be simply ignored when creating a SAS table and a variable with length of 8 bytes gets created (which is actually a good thing).

For DATE variables: You probably want to define a format so that the SAS date values become human readable for printing.

Amending the code you've posted below will work.

proc sql;
  create table employeesdata
    (employee_id         num,
    first_name           char(15) ,
    last_name            char(18),  
    email                char(18) ,
    phone_number         num(18)    ,
    hire_date            date format=date9., 
    job_id               num   ,     
    salary               num  ,   
    commission_pct       num , 
    manager_id           num,  
    department_id        num
    )
    ;
  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123 ,'17jun2003'd,  123  ,24000, 0, 0 ,90);
quit;

Re: insert values in proc sql

Kurt_Bremser — Sun, 13 Oct 2019 10:00:39 GMT

And if your goal is to create a dataset from literals, the data step is the way to go:

data employeesdata;
input
  employee_id :$3.
  first_name :$15.
  last_name :$18.
  email :$18.
  phone_number :$18.
  hire_date :ddmmyy10.	
  job_id :$9.  		
  salary	
  commission_pct
  manager_id :$8.
  department_id :$8.
;
format hire_date ddmmyyd10.;
datalines;
100 steven king sking 515.123.4567 17-06-2003 adpres 24000 0 0 90
;

It is much easier to add additional datalines than insert statements in SQL.

Edit: changed the last two columns to character, as they obviously contain ID values. Set the correct length as per data specifications.

Re: insert values in proc sql

BrahmanandaRao — Sun, 13 Oct 2019 10:12:35 GMT

sir

suppose i want decimals in

commission_pct(0.00)

insert into employeesdata values(

100 steven king sking 515.123.4567 17-06-2003 adpres 24000 0.00 0 90

commission_pct num(3 , 6)not working

how to solve it

Re: insert values in proc sql

Patrick — Sun, 13 Oct 2019 10:26:45 GMT

@BrahmanandaRao

The one thing is how SAS stores values internally. For this you just need to pass in a valid string (and 0.00 is valid), the other thing is how SAS prints the values if you don't define an explicit format. If you want anything else than the SAS default for numerical variables (which I believe is BEST32.) then define the format explicitly for the variable when creating the table. This makes the format an attribute of the column and SAS will use it whenever you "look" at the data without defining another format explicitly.

proc sql;
  create table employeesdata
    (employee_id         num,
    first_name           char(15) ,
    last_name            char(18),  
    email                char(18) ,
    phone_number         num(18)    ,
    hire_date            date format=date9., 
    job_id               num   ,     
    salary               num  ,   
    commission_pct       num format=16.2, 
    manager_id           num,  
    department_id        num
    )
    ;
  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123 ,'17jun2003'd,  123  ,24000, 0.05, 0 ,90);
  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123 ,'17jun2003'd,  123  ,24000, 0, 0 ,90);
quit;
proc print data=employeesdata;
run;

Re: insert values in proc sql

Tom — Sun, 13 Oct 2019 15:19:23 GMT

@BrahmanandaRao wrote:

sir

suppose i want decimals in
commission_pct(0.00)
insert into employeesdata values(
100 steven king sking 515.123.4567 17-06-2003 adpres 24000 0.00 0 90
commission_pct num(3 , 6)not working

how to solve it

It is not at all clear what you are asking.

Remember that SAS stores only two types of variables, fixed length character strings and floating point numbers. You can use any valid number representation in the VALUES() list, the same as you would use in any SAS expression. Character values must be enclosed in quotes. SAS stores dates as numbers, so to insert a date either enter the actual number of days SAS uses for that date, or use a date literal which is a quoted string in a style that the DATE informat can read with the letter D appended after the closing quote, like '17jun2003'd.

If you want the change how the values are DISPLAYed then attach a FORMAT to the variable. So use a date type format for your date values. You could use the 4.2 format to display percentages with two decimal places.