proc sql;
insert into _SYSTEM.MBNA_HD_RUNPARMS
set PARAMETER='HIST_START_DT'
,DESC='Start Date for history build'
,TYPE='D'
,CVAL=''
,NVAL=''
,DVAL='01AUG2014:00:00:00'
set PARAMETER='HIST_END_DT'
,DESC='End Date for history build'
,TYPE='D'
,CVAL=''
,NVAL=''
,DVAL='01AUG2020:00:00:00';
select
PARAMETER
,TYPE
,CVAL
,NVAL
,DVAL format=DATETIME20.,
from _SYSTEM.MBNA_HD_RUNPARMS
;
QUIT;
ERROR: Character expression requires a character format.
ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.
Hi,
I'm trying to add these rows to this table, I've tried using this code to get it added but I'm receiving this error. How can I re-write this, so itll work?
Kind regards,
Thanks
'01AUG2014:00:00:00'
is just a string, or a character value in SAS parlance. If you wanted to specify a datetime constant, you need to add the dt modifier to the string:
'01AUG2014:00:00:00'dt
If that does not fix it:
1 The SAS System 13:17 Friday, September 11, 2020
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 LIBNAME _SYSTEM BASE "/team/dev/bicc_admin/LM_TRAN/system";
NOTE: Libref _SYSTEM refers to the same physical library as _SYSD.
NOTE: Libref _SYSTEM was successfully assigned as follows:
Engine: BASE
Physical Name: /team/dev/bicc_admin/LM_TRAN/system
27
28
29 proc sql;
30 insert into _SYSTEM.MBNA_HD_RUNPARMS
31 set PARAMETER='HIST_START_DT'
32 ,DESC='Start Date for history build'
33 ,TYPE='D'
34 ,CVAL=''
35 ,NVAL=''
36 ,DVAL='01AUG2014:00:00:00'dt
37 set PARAMETER='HIST_END_DT'
38 ,DESC='End Date for history build'
39 ,TYPE='D'
40 ,CVAL=''
41 ,NVAL=''
42 ,DVAL='01AUG2020:00:00:00'dt;
ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.
ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43
44 select
45 PARAMETER
46 ,TYPE
47 ,CVAL
48 ,NVAL
2 The SAS System 13:17 Friday, September 11, 2020
49 ,DVAL format=DATETIME20.,
50 from _SYSTEM.MBNA_HD_RUNPARMS
_______
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
51 ;
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
52 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5360.21k
OS Memory 25512.00k
Timestamp 11/09/2020 01:22:40 PM
Step Count 5 Switch Count 0
Page Faults 0
Page Reclaims 26
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 0
53
54 GOPTIONS NOACCESSIBLE;
55 %LET _CLIENTTASKLABEL=;
56 %LET _CLIENTPROCESSFLOWNAME=;
57 %LET _CLIENTPROJECTPATH=;
58 %LET _CLIENTPROJECTPATHHOST=;
59 %LET _CLIENTPROJECTNAME=;
60 %LET _SASPROGRAMFILE=;
61 %LET _SASPROGRAMFILEHOST=;
62
63 ;*';*";*/;quit;run;
64 ODS _ALL_ CLOSE;
65
66
67 QUIT; RUN;
68
This is the log
This line
,DVAL format=DATETIME20.,
has a comma at the end that you do not want. With a comma the next thing expected is another expression or variable name but you have the keyword FROM indicating that almost certainly you are indicating the data sets next.
And likely you have more variables that are numeric that you are attempting to assign character values to.
My guess is one or more of the =' ' should be = . to assign missing to numeric. But we don't know your data set. Again, my guess, would be Nval as the name sort of looks like short for "numericalvalue" and Cval .
Run Proc contents on your data set to determine variable types.
2 | DESC | Char | 256 | Description | |
6 | DVAL | Num | 8 | DATETIME20. | DateTime Value |
5 | NVAL | Num | 8 | Numeric Value | |
1 | PARAMETER | Char | 32 | Parameter (SOURCE_TYPE_LOCATION_NAME) | |
3 | TYPE | Char | 1 | Type |
NVAL is numeric:
5 | NVAL | Num | 8 | Numeric Value |
but you try to assign it a character value:
,NVAL=''
that won't work.
@guest1994 wrote:
This is the proc contents:
2 DESC Char 256 Description 6 DVAL Num 8 DATETIME20. DateTime Value 5 NVAL Num 8 Numeric Value 1 PARAMETER Char 32 Parameter (SOURCE_TYPE_LOCATION_NAME) 3 TYPE Char 1 Type
And Cval? Which might be the variable order 4? Is it Char or Num?
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: