BookmarkSubscribeRSS Feed
msharma1788
Calcite | Level 5

Data Set name is PLN200

SSN              PLAN_ID    CV_CAT

001                9000             21
001                9020            847 

 

My code is not giving me expected result:

DATA PLN200;                                      
SET PLN200;                                       
BY SSN;                                           
IF PLAN_ID = 9000 AND PLAN_ID = 9020 THEN DO;     
IF CV_CAT IN(26,55,65,817,827,847) THEN DELETE;   
END; RUN;                                         

 

I am expecting  only "001                9000             21 "   in result.

 

Please suggest.

6 REPLIES 6
Astounding
PROC Star

It is not possible for PLAN_ID to be equal to both 9000 and 9020.

 

Use OR instead of AND, or better yet:

 

IF PLAN_ID in (9000, 9020) THEN DO;    

 

 

msharma1788
Calcite | Level 5

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.

Reeza
Super User

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.

 

Astounding
PROC Star

A DATA step is limited to processing one observation at a time.  Here's a way to program around that for your purposes:

 

data want;

merge

pln200 (where=(plan_id=9000 in=in_9000))

pln200 (where=(plan_id=9020 n=in_9020))

pln200;

by ssn;

if in_9000 and in_9020 and CV_CAT IN(26,55,65,817,827,847) THEN DELETE;

run;

 

The order of the data sets in the MERGE statement is important.  The PLN200 with no WHERE condition must be last.

Haikuo
Onyx | Level 15

Simply,

IF PLAN_ID = 9000 OR PLAN_ID = 9020 THEN DO;   

 

You also can check the condition at the same time:

DATA PLN200;                                      
SET PLN200;                                       
BY SSN;                                           
IF not ( PLAN_ID in ( 9000, 9020) and   
CV_CAT IN(26,55,65,817,827,847)) ;   
RUN;                

 

SuryaKiran
Meteorite | Level 14

Hi,

 

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;
Thanks,
Suryakiran

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 805 views
  • 0 likes
  • 5 in conversation