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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.