topic Re: INPUT function requires a character argument in SAS Programming

INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 12:53:26 GMT

On running the code below I have the following error messages. Can anyone help with correcting the code

ERROR: INPUT function requires a character argument.

ERROR: Expression using greater than or equal (>=) has components that are of different data types.

%let user = xxxx;

LIBNAME mdcrd_vw TERADATA SCHEMA=xxx_vdm_view_mdcr_prd

SERVER="xxxprodp.xxxsvc.local" CONNECTION=global AUTHDOMAIN="TD_&SYSUSERID.";

%macro Zip5 (start_dt, end_dt);

PROC SQL;

DROP TABLE nfpp_trd.&user._BENE_2015_3_all;

CREATE TABLE nfpp_trd.&user._bENE_2015_3_all

(DBCREATE_TABLE_OPTS='PRIMARY INDEX(BENE_SK)') AS

SELECT DISTINCT T1.GEO_sk AS bene_zip_cd,

T1.GEO_ZIP4_CD,

T1.BENE_sk,

T2.GEO_FIPS_CNTY_CD,

t2.GEO_FIPS_STATE_CD

FROM mdcrd_vw.V2_MDCR_BENE_FCT_TRANS_HSTRY t1 LEFT JOIN

mdcrd_vw.V2_MDCR_GEO_ZIP9_CD T2 ON (T1.GEO_sk=T2.GEO_sk AND T1.GEO_ZIP4_CD=T2.GEO_ZIP4_CD)

WHERE

BENE_PTA_STUS_CD ='Y'

AND BENE_PTB_STUS_CD = 'Y'

AND BENE_CVRG_TYPE_CD = '9'

and T1.BENE_DEATH_DT = .

and T1.IDR_LTST_TRANS_FLG = 'Y'

and t1.bene_fct_efctv_dt between input(&start_dt.,date9.) and input(&end_dt.,date9.)

and t1.BENE_FCT_OBSLT_DT >=input(&start_dt.,date9.);

QUIT;

%mend zip5;

%zip5 ("01JAN2019"d, "31DEC2019"d);

Re: INPUT function requires a character argument

Kurt_Bremser — Wed, 26 Aug 2020 12:59:55 GMT

Please post the whole log of the step by copy/pasting it into a window opened with this button:

Since the position of the ERROR indicator is then kept, it makes it much easier for use to spot the problem point.

Re: INPUT function requires a character argument

PaigeMiller — Wed, 26 Aug 2020 13:00:18 GMT

Input function requires a character argument.

So, here, your first argument to INPUT is not character. What are the values of these macro variables?

and t1.bene_fct_efctv_dt between input(&start_dt.,date9.) and input(&end_dt.,date9.)
and t1.BENE_FCT_OBSLT_DT >=input(&start_dt.,date9.);

Re: INPUT function requires a character argument

Kurt_Bremser — Wed, 26 Aug 2020 13:04:49 GMT

and t1.bene_fct_efctv_dt between input(&start_dt.,date9.) and input(&end_dt.,date9.)
and t1.BENE_FCT_OBSLT_DT >=input(&start_dt.,date9.);

This seem to be the offending parts.

Since you call the macro with date literals:

%zip5 ("01JAN2019"d, "31DEC2019"d);

which are immediately converted to numeric SAS date values, the INPUT function is not needed. It would be needed if you had omitted the trailing "d" characters that make a string a date literal.

So you can simplify the offending lines to:

and t1.bene_fct_efctv_dt between &start_dt. and &end_dt.
and t1.BENE_FCT_OBSLT_DT >= &start_dt.;

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 13:11:21 GMT

Attached is the log

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 13:14:21 GMT

Start date is 01Jan2019 and end date is 31Dec2019.
So am I changing this

&start_dt to 01jan2019

Re: INPUT function requires a character argument

Kurt_Bremser — Wed, 26 Aug 2020 13:26:06 GMT

@onyeajam wrote:
Attached is the log

Ah ... no?

Just copy/paste it into a code box as requested, no need for attaching anything.

Re: INPUT function requires a character argument

PaigeMiller — Wed, 26 Aug 2020 13:27:13 GMT

@onyeajam wrote:
Start date is 01Jan2019 and end date is 31Dec2019.
So am I changing this

&start_dt to 01jan2019

I think you have missed the point. You don't have to change the values of the macro variables. You just remove the INPUT function, as shown by @Kurt_Bremser

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 13:29:21 GMT

I have corrected the statements as recommended to

and t1.bene_fct_efctv_dt between &start_dt. and &end_dt.
and t1.BENE_FCT_OBSLT_DT >= &start_dt.;

But a New Error Message

Note: Line generated by the macro var "START_DAT".
input "01JAN2019"d
Error 22-322: syntax error, expecting one of the following: !, !!, &, *,
**, +, -,?,<.<=, <>, =, >, >=, AND, EQ, EQT, GE, GET, GROUP, GT, GTT,
HAVING, LE, LET, LT, LTT, NE, NET, OR< ORDER, ^=, |, ||, ~=.
Error 76-322: syntax error, statement will be ignored

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 13:32:21 GMT

me bad.
That is what I had in the attachment. But I made the corrections you
suggested and a new error like I said in my last reply

1 The SAS
System 08:10 Wednesday, August 26, 2020

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='marketsaturation.sas';

4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';

5 %LET _CLIENTPROJECTPATH='';

6 %LET _CLIENTPROJECTPATHHOST='';

7 %LET _CLIENTPROJECTNAME='';

8 %LET _SASPROGRAMFILE='\\Client\H$\Secure\Market
Saturation\marketsaturation.sas';

9 %LET _SASPROGRAMFILEHOST='CONCTXxxx';

10

11 ODS _ALL_ CLOSE;

12 OPTIONS DEV=ACTIVEX;

13 GOPTIONS XPIXELS=0 YPIXELS=0;

14 %macro HTML5AccessibleGraphSupported;

15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then
ACCESSIBLE_GRAPH;

16 %mend;

17 FILENAME EGSR TEMP;

18 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

19 STYLE=HTMLBlue

20 NOGTITLE

21 NOGFOOTNOTE

22 GPATH=&sasworklocation

23 ENCODING=UTF8

24 options(rolap="on")

25 ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

26

27 %let user = xxxx

28

29

30 LIBNAME mdcrd_vw TERADATA SCHEMA=xxx_vdm_view_mdcr_prd

31 SERVER="xxxprodp.xxxsvc.local" CONNECTION=global
AUTHDOMAIN="TD_&SYSUSERID.";

NOTE: Credential obtained from SAS metadata server.

NOTE: Libref MDCRD_VW was successfully assigned as follows:

Engine: TERADATA

Physical Name: xxxprodp.xxxsvc.local

32

33 %macro Zip5 (start_dt, end_dt);

34

35

41 PROC SQL;

42 DROP TABLE nfpp_trd.&user._BENE_2015_3_all;

43 CREATE TABLE nfpp_trd.&user._bENE_2015_3_all

44 (DBCREATE_TABLE_OPTS='PRIMARY INDEX(BENE_SK)') AS

45 SELECT DISTINCT T1.GEO_sk AS bene_zip_cd,

46 T1.GEO_ZIP4_CD,

47 T1.BENE_sk,

48 T2.GEO_FIPS_CNTY_CD,

49 t2.GEO_FIPS_STATE_CD

50 FROM mdcrd_vw.V2_MDCR_BENE_FCT_TRANS_HSTRY t1 LEFT JOIN

51 mdcrd_vw.V2_MDCR_GEO_ZIP9_CD T2 ON (T1.GEO_sk=T2.GEO_sk
AND T1.GEO_ZIP4_CD=T2.GEO_ZIP4_CD)

52 WHERE

53 2 The
SAS System 08:10 Wednesday, August 26, 2020

54

55 BENE_PTA_STUS_CD ='Y'

56 AND BENE_PTB_STUS_CD = 'Y'

57 AND BENE_CVRG_TYPE_CD = '9'

58 and T1.BENE_DEATH_DT = .

59

60 and T1.IDR_LTST_TRANS_FLG = 'Y'

61 and t1.bene_fct_efctv_dt between input(&start_dt.,date9.)
and input(&end_dt.,date9.)

62 and t1.BENE_FCT_OBSLT_DT >=input(&start_dt.,date9.);

63

64

65 QUIT;

66 %mend zip5;

67 %zip5 ("01JAN2019"d, "31DEC2019"d);

WARNING: File NFPP_TRD.PUX2_BENE_2015_3_all.DATA does not exist.

WARNING: Table NFPP_TRD.PUX2_BENE_2015_3_all has not been dropped.

ERROR: INPUT function requires a character argument.

ERROR: INPUT function requires a character argument.

ERROR: Expression using BETWEEN has components that are of different data
types.

ERROR: INPUT function requires a character argument.

ERROR: Expression using greater than or equal (>=) has components that are
of different data types.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.59 seconds

cpu time 0.02 seconds

68

69 %LET _CLIENTTASKLABEL=;

70 %LET _CLIENTPROCESSFLOWNAME=;

71 %LET _CLIENTPROJECTPATH=;

72 %LET _CLIENTPROJECTPATHHOST=;

73 %LET _CLIENTPROJECTNAME=;

74 %LET _SASPROGRAMFILE=;

75 %LET _SASPROGRAMFILEHOST=;

76

77 ;*';*";*/;quit;run;

78 ODS _ALL_ CLOSE;

79

80

81 QUIT; RUN;

82

Re: INPUT function requires a character argument

Kurt_Bremser — Wed, 26 Aug 2020 13:32:22 GMT

I really hate to repeat myself, but:

POST THE WHOLE LOG AS ASKED!!

You left the keyword INPUT somewhere in your code where it's not needed, but I can't tell you where without seeing the WHOLE log. I hope you get the message now.

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 13:38:21 GMT

Sorry KurtBremser

I am working with 2 PCs that are not communicating with each other.
I did not eliminate the last input, the reason for the new error I
believe. I have corrected and am rerunning. Will update you soon if
resolved or not.

Thanks

Re: INPUT function requires a character argument

Tom — Wed, 26 Aug 2020 14:33:57 GMT

Turn on the MPRINT option before calling the macro. That way the SAS log will reflect the SAS code that the macro generated.

options mprint;
%zip5 ("01JAN2019"d, "31DEC2019"d);

So with your original macro code and macro call you should see something like this in the log:

MPRINT(ZIP5)  ....  input("01JAN2019"d,date9.) and input("31DEC2019"d,date9.) and t1.BENE_FCT_OBSLT_DT >=input("01JAN2019"d,date9.);

Which is trying to execute read from a date value instead of a character string.

If you change the call to use a quoted string instead of a date literal

%zip5 ("01JAN2019", "31DEC2019");

Then the code will look like this:

MPRINT(ZIP5)  ....  input("01JAN2019",date9.) and input("31DEC2019",date9.) and t1.BENE_FCT_OBSLT_DT >=input("01JAN2019",date9.);

Which is valid.

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 14:55:48 GMT

Its an endless running of the program after i removed the input statment in offeding lines. Thus I had to kill the program.

and t1.bene_fct_efctv_dt between &start_dt. and &end_dt.

and t1.BENE_FCT_OBSLT_DT >= &start_dt.;

QUIT;

%mend zip5;

%zip5 ("01JAN2019"d, "15JAN2019"d);

Re: INPUT function requires a character argument

Kurt_Bremser — Wed, 26 Aug 2020 15:05:45 GMT

Where does library

nfpp_trd

point to?

Is the DISTINCT in the SELECT necessary?

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 15:15:47 GMT

@Tom
I went back to original code and change the call to use a quoted string instead of a date literal . That does not work too. Endless running of the program
and t1.bene_fct_efctv_dt between input(&start_dt.,date9.) and input(&end_dt.,date9.)
and t1.BENE_FCT_OBSLT_DT >=input(&start_dt.,date9.);
QUIT;
%mend zip5;
%zip5 ("01JAN2019", "31DEC2019");

Re: INPUT function requires a character argument

PaigeMiller — Wed, 26 Aug 2020 15:20:48 GMT

Although this has been mentioned several times before, you just take out the INPUT function, and your code will work.

and t1.bene_fct_efctv_dt between &start_dt and &end_dt 
and t1.BENE_FCT_OBSLT_DT >=&start_dt;
...
%zip5 ("01JAN2019"d, "31DEC2019"d);

Re: INPUT function requires a character argument

onyeajam — Wed, 26 Aug 2020 15:23:34 GMT

Thanks @PaigeMiller
I did that and the program wont stop running. It was running for 20 minutes with nothing happening, so I KILLED IT

Re: INPUT function requires a character argument

PaigeMiller — Wed, 26 Aug 2020 15:26:23 GMT

SHOW US the actual code you are using. Just saying it didn't work tells us nothing. Also, how do you know this is a problem with the code? It could be that the TERADATA server is taking 20 minutes or more to do the extract.

Re: INPUT function requires a character argument

Tom — Wed, 26 Aug 2020 15:36:59 GMT

@onyeajam wrote:
@Tom
I went back to original code and change the call to use a quoted string instead of a date literal . That does not work too. Endless running of the program
and t1.bene_fct_efctv_dt between input(&start_dt.,date9.) and input(&end_dt.,date9.)
and t1.BENE_FCT_OBSLT_DT >=input(&start_dt.,date9.);
QUIT;
%mend zip5;
%zip5 ("01JAN2019", "31DEC2019");

So you have solved this issue and are now on to a new issue. Please open a new thread to talk about how to optimize queries using remote databases. Here is a suggestion to try : It looks like you are joining two tables in your remote database so just use explicit passthru SQL to run the query on the database instead. In that case you will want to replace the hardcoded date values with values that work with the SQL dialect used by that database.