Need help with concantenation

[mahamza]

Hi,

I m trying to concatenate few strings so that the output looks like following. 

ACT_D:20230402

The syntax I was using is 'ACT_D:'||PUT(Complete_Full_Date, yymmddn8.) as REF4,

but it gives the error as 

ERROR 22-322: Syntax error, expecting one of the following: (, ).

ERROR 76-322: Syntax error, statement will be ignored.

The complete code is as following: 

proc sql;
create table FORMATED_COMP AS
SELECT Order_Number AS Invoice,
&Paydate as Pay_Date ,
EMPLOYEE_PEIN as Employee ,
1 as Item ,
'COSBI' as REASON_CODE ,
Channel_Level_2 as LC,
ISSUED_FULL_DATE AS SALE_DATE format date9.,
'' as ESN ,
'' AS TEXT1 ,
'' AS TEXT2 ,
'' AS POSSKU ,
'LOB:DSL' AS REF1 ,
Product_Description AS REF2,
CASE WHEN PRODUCT_CODE IN (
'1-3K1XCP0',
'1-3K1XCOB',
'1-J6FZOO-C17',
'1-J6G02L-C17',
'1-J6HCI9-C17',
'1-J6HCI9-C17LOB',
'1-J6HCKD-C17',
'1-J6FZWE-C17') THEN 'Internet 5, 5 Plus or' ELSE 'Internet 100+'
END as REF3,
'ACT_D:'||PUT(Complete_Full_Date, yymmddn8.) as REF4,
'BRS Base Commission' as REF5 ,
'' as REF6,
'' as REF7,
'' as REF8,
1 AS Volume ,
'' as Base_package_Price ,
Commission AS Amount,
'' as Amount2 ,
Dealer_NM AS Corp_store_name,
Province_Code as Region_Name,
'' as Employee_Name ,
'SB Internet Commission' as DESCRIPTION,
EMP_TITLE_EN_DESC AS JOB_NAME,
case when EMP_TITLE_EN_DESC = 'Sales Consultant - BRS Retail' then 'SP' else 'NSP' end as JGRP,
COMPLETE_FULL_DATE AS Activation_Dt format date9.
'' as Paid_sts,
'' as Status
FROM Employee_info t1 ;
RUN;

Will appreciate the help. Thanks .

I am using SAS EG. 

[Cynthia_sas]

Hi: My guess is that there's some other error in your code. Without seeing your full log, however, it's impossible to guess where the error might be. If I had to take a guess, I would expect that the previous
&paydate as Pay_Date,
is probably the place where the error messages start. But that is just a guess. You don't show what is the value for the macro variable &paydate, however, I can generate a nice error like this without doing a concatenate and using SASHELP.CLASS:

But I can fix the issue if I tell PROC SQL to treat the macro variable as a date constant:

Also note that PROC SQL ends correctly with a QUIT; not with a RUN; You should be seeing this message in the log:

For more help, please explain your use of Macro variables post all of your code and some sample data for people to use. Without data, we don't know whether your date values are character or numeric or what the macro variables contain.

Cynthia

Cynthia

[Tom]

Reformat your code so it is easier for Humans to scan and the missing comma before   COMPLETE_FULL_DATE AS Activation_Dt format date9.

in your SELECT clause will be more obvious.

create table FORMATED_COMP AS
  SELECT Order_Number AS Invoice
  , &Paydate as Pay_Date 
  , EMPLOYEE_PEIN as Employee 
  ....
  , case when EMP_TITLE_EN_DESC = 'Sales Consultant - BRS Retail' then 'SP' else 'NSP' end as JGRP
  , COMPLETE_FULL_DATE AS Activation_Dt format date9.
  , ' ' as Paid_sts
  , ' ' as Status
  FROM Employee_info t1 
;

[ballardw · Accepted solution]

Any time you have an error or other message in the Log that you want help with then copy the submitted code for the procedure or data step and all of the associated messages. Then on the forum open a text box and paste the text.

The text box is important because it preserves formatting of diagnostic characters that SAS often provides AND then we can make corrections/suggestions by copying text, editing and pasting the modified text for you to use.

An example, different error but same cause: missing comma between select elements. Even with a different error we can tell where in code the issue is because we don't have to parse lots of stuff.  We can see it is likely around the "ABC:"

466  proc sql;
467     create table junk as
468     select sex
469     "ABC:"||put(weight,f5.1) as ref
              --
              22
              76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT,
              FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

470     from sashelp.class
471     ;
472  quit;