02-21-2018 01:39 PM
Data Set name is PLN200
SSN PLAN_ID CV_CAT
001 9000 21
001 9020 847
My code is not giving me expected result:
IF PLAN_ID = 9000 AND PLAN_ID = 9020 THEN DO;
IF CV_CAT IN(26,55,65,817,827,847) THEN DELETE;
I am expecting only "001 9000 21 " in result.
02-21-2018 02:00 PM
I need the data to be deleted only when the SSN is in both the plans 9000 and 9020 , If its in both the plans then it should check the CV_CAT ids mentioned. and should delete that row.
02-21-2018 02:03 PM
So you need to check multiple rows first to see if that SSN has both 9000 and 9020, does that sound correct?
Since all the data is not in one row your logic needs to be significantly different.
02-21-2018 02:07 PM
A DATA step is limited to processing one observation at a time. Here's a way to program around that for your purposes:
pln200 (where=(plan_id=9000 in=in_9000))
pln200 (where=(plan_id=9020 n=in_9020))
if in_9000 and in_9020 and CV_CAT IN(26,55,65,817,827,847) THEN DELETE;
The order of the data sets in the MERGE statement is important. The PLN200 with no WHERE condition must be last.
02-21-2018 01:46 PM
IF PLAN_ID = 9000 OR PLAN_ID = 9020 THEN DO;
You also can check the condition at the same time:
IF not ( PLAN_ID in ( 9000, 9020) and
CV_CAT IN(26,55,65,817,827,847)) ;
02-21-2018 04:17 PM
Check this one using CALL SYMPUT
DATA HAVE; INFILE DATALINES dlm=" "; INPUT SSN PLAN_ID CV_CAT; datalines; 001 9000 21 001 9020 847 ; run; proc sort data=HAVE; by SSN PLAN_ID; run; DATA WANT; SET HAVE; BY SSN PLAN_ID; IF FIRST.SSN THEN CALL SYMPUT("Hold"," "); IF PLAN_ID=9000 THEN CALL SYMPUT("Hold","YES"); IF PLAN_ID=9020 and SYMGET("Hold")="YES" and CV_CAT IN(26,55,65,817,827,847) THEN DELETE; RUN;