topic Re: Concatenating string with text and values for lookup in SAS Programming

Concatenating string with text and values for lookup

supersasnewbie — Fri, 05 Aug 2022 12:38:24 GMT

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;

Re: Concatenating string with text and values for lookup

maguiremq — Fri, 05 Aug 2022 13:42:20 GMT

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

Re: Concatenating string with text and values for lookup

Tom — Fri, 05 Aug 2022 14:42:59 GMT

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
;