Concatenating string with text and values for lookup

[supersasnewbie]

Hello,

I am a new user to SAS, and I would like to concatenate two fields as a variable that I can look up in my query. I'm not 100% on this, but I wonder if the issue is due to the Product Name being in Text format, while Serial Number is in Values, causing the concatenation to fail. Can someone help me understand this better? Below is the code I'm using:

proc sql;

CREATE TABLE PROD_SN AS

SELECT T.PRODUCTNM
            ,T.SERIALNBR
            ,T.MKTID
            ,T.SCHEDULETYPE
            ,CATX(“_”, T.PRODUCTNM, T.SERIALNBR) AS PROD_SN

FROM tbl.ProductDetail T

WHERE PROD_SN in (‘CONT_123’,’CONT_233’,’STAR_4329’)

GROUP BY T.PRODUCTNM
            ,T.SERIALNBR
            ,T.MKTID
            ,T.SCHEDULETYPE
            ,CATX(“_”, T.PRODUCTNM, T.SERIALNBR) AS PROD_SN

;
RUN;

[maguiremq]

Can you please post your log? Copy just the part with the error, and paste it using the `</>` icon section of your reply.

[Tom]

The WHERE clause is the problem.  You cannot reference a variable that does not yet exist like that.

Here is a simpler example:

1099  proc sql;
1100  create table want as
1101  select *,age*age as agesq from sashelp.class
1102  where agesq in (121)
1103  ;
ERROR: The following columns were not found in the contributing tables: agesq.

You can just add the CALCULATED keyword to let PROC SQL know that you want it to use the new derived variable and not something that was on the inputs.

1104  proc sql;
1105  create table want as
1106  select *,age*age as agesq from sashelp.class
1107  where calculated agesq in (121)
1108  ;
NOTE: Table WORK.WANT created, with 2 rows and 6 columns.

 So try:

CREATE TABLE PROD_SN AS
SELECT T.PRODUCTNM
     , T.SERIALNBR
     , T.MKTID
     , T.SCHEDULETYPE
     , CATX('_', T.PRODUCTNM, T.SERIALNBR) AS PROD_SN
FROM tbl.ProductDetail T
WHERE calculated PROD_SN in ('CONT_123','CONT_233','STAR_4329')
GROUP BY T.PRODUCTNM
       , T.SERIALNBR
       , T.MKTID
       , T.SCHEDULETYPE
       , calculated PROD_SN
;