BookmarkSubscribeRSS Feed
Saba1
Quartz | Level 8

Hi

I have the following dataset:

 

Data have;
input ID Date: mmddyy10. Catg_1 Catg_2 ;
datalines;
0001 03/31/2001 GLM .
0001 06/30/2001 . .
0001 12/31/2002 LGE .
0001 09/30/2003 LGE .
0001 06/30/2004 LGE .
0002 03/31/2005 . SC
0002 06/30/2005 . SC
0002 06/30/2005 EIE .
0003 09/30/2017 . .
0004 03/31/2007 . .
0004 12/31/2007 . GR
0004 06/30/2009 G GR
0004 09/30/2010 . .
;
run;

What I Want is as follows:

 

Data Want;
input ID Date: mmddyy10. Catg_1 Catg_2 ;
datalines;
0001 03/31/2001 GLM .
0001 12/31/2002 LGE .
0002 03/31/2005 . SC
0002 06/30/2005 EIE .
0003 09/30/2017 . .
0004 12/31/2007 . GR
0004 06/30/2009 G .
;
run;

Irrespective of the Date, if both category variables (Catg_1 & Catg_2) are missing for an ID, one row with missing observations must be selected (i.e. ID=0003). If both category variables have an observation on the same date, same ID; Catg_1 observation (row) must be selected (i.e. ID=0004, date= 06/30/2009). Repeated observations of either of the category variables for a single ID must be chosen once only. However, if they are different, then the row must be selected when they are different than the previous one, for same ID (i.e. ID=0001)

 

Thanks.

1 REPLY 1
saivenkat
Obsidian | Level 7
data want(drop=lID lCatg_1 lCatg_2);
set have;
by id;
lID=lag(ID);
lCatg_1=lag(Catg_1);
lCatg_2=lag(Catg_2);
if lid eq id then do;
	if lCatg_1 eq Catg_1 and lCatg_2 eq Catg_2 then delete;
	if lCatg_1 ne Catg_1 and lCatg_2 eq Catg_2 then Catg_2='';
	if lCatg_1 eq Catg_1 and lCatg_2 ne Catg_2 then Catg_1='';
	if Catg_1 eq '' and Catg_2 eq '' then delete;
/*	if lCatg_2 eq Catg_2 then delete;		 */
end;
if not (first.id and last.id) and Catg_1 eq '' and Catg_2 eq '' then delete;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1042 views
  • 1 like
  • 2 in conversation