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;
Can you please post your log? Copy just the part with the error, and paste it using the `</>` icon section of your reply.
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
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.