I am working on the below sql and sub-sql statement:
PROC SQL;
CREATE TABLE JR.PEAK AS
SELECT s.USUBJID FROM JR.SV_SRC s WHERE s.SVSTDTC NE "" AND s.SVPENRFN=2
;
CREATE TABLE JR.SV_SRC_STEP1 AS
SELECT SV.*
, ( SELECT ( CASE WHEN SV.USUBJID IN (SELECT p.USUBJID FROM JR.PEAK p) THEN "Y" ELSE "N" END ) ) AS P_2
FROM JR.SV SV
;
QUIT;
It gives me the below error msg:
270 CREATE TABLE JR.SV_SRC_STEP1 AS
271 SELECT SV.*
272 , ( SELECT ( CASE WHEN SV.USUBJID IN (SELECT p.USUBJID FROM JR.PEAK p) THEN "Y" ELSE "N" END ) ) AS PERIOD2
_
22
76
272 ! LABEL="Assigned to Period 2 (Y/N)"
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
Any help or suggestions will be appreciated!
I think your query could be simplified to :
PROC SQL;
CREATE TABLE JR.PEAK AS
SELECT USUBJID
FROM JR.SV_SRC
WHERE SVSTDTC NE "" AND SVPENRFN=2;
CREATE TABLE JR.SV_SRC_STEP1 AS
SELECT
*,
CASE
WHEN USUBJID IN (SELECT USUBJID FROM JR.PEAK) THEN "Y"
ELSE "N" END AS P_2
FROM JR.SV;
QUIT;
(untested)
Wouldn't a join be easier?
I think your query could be simplified to :
PROC SQL;
CREATE TABLE JR.PEAK AS
SELECT USUBJID
FROM JR.SV_SRC
WHERE SVSTDTC NE "" AND SVPENRFN=2;
CREATE TABLE JR.SV_SRC_STEP1 AS
SELECT
*,
CASE
WHEN USUBJID IN (SELECT USUBJID FROM JR.PEAK) THEN "Y"
ELSE "N" END AS P_2
FROM JR.SV;
QUIT;
(untested)
Or very simply:
proc sql; create table JR.SV_SRC_STEP1 as select *, case when USUBJID in (select USUBJID from JR.SV_SRC where SVSTDTC ne "" and SVPENRFN=2) then "Y" else "N" end AS P_2 from JR.SV; quit;
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.