topic Re: How I can insert these two extra rows into this table? in SAS Programming

How I can insert these two extra rows into this table?

guest1994 — Fri, 11 Sep 2020 11:25:33 GMT

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

Re: How I can insert these two extra rows into this table?

Kurt_Bremser — Fri, 11 Sep 2020 11:34:04 GMT

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

post the complete log of the SQL procedure step
post the output of PROC CONTENTS for your dataset

Re: How I can insert these two extra rows into this table?

guest1994 — Fri, 11 Sep 2020 12:25:30 GMT

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

Re: How I can insert these two extra rows into this table?

guest1994 — Fri, 11 Sep 2020 12:30:49 GMT

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

Re: How I can insert these two extra rows into this table?

ballardw — Fri, 11 Sep 2020 14:46:42 GMT

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.

Re: How I can insert these two extra rows into this table?

Kurt_Bremser — Fri, 11 Sep 2020 15:54:38 GMT

NVAL is numeric:

NVAL

Num

Numeric Value

but you try to assign it a character value:

,NVAL=''

that won't work.

Re: How I can insert these two extra rows into this table?

ballardw — Fri, 11 Sep 2020 16:08:27 GMT

@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?