BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
altijani
Quartz | Level 8

Hi Community,

 

I have the following unsorted data:

Month   Variable1

Jan        123456789

Feb        123456789

Mar        123456789

Jan         123456-P

Jan         123456

Feb        123456-PCLOSED

Jan        123456CLOSED

Jan        987654321

Feb       123456789CLOSED

Jan        25648985

Mar       123456789CLOSED

Apr        123456789CLOSED

 

 

Which I need to sort in this order (each observation ending with CLOSED to be following the observation without CLOSED):

Month Variable1

Jan      123456

Feb      123456CLOSED

Jan      123456-P

Feb      123456-PCLOSED

Jan      123456789

Feb      123456789CLOSED

Feb      123456789

Mar      123456789CLOSED

Mar      123456789

Apr      123456789CLOSED

Jan      25648985

Jan      987654321

 

The aim of the sort is because I need to keep in my data the observations with the following conditions:

1. If the observation has both CLOSED and the previous without CLOSED at the end, keep the one with CLOSED. In other words, drop the observations that do not have CLOSED at the end

2. If the observation hasn't a CLOSED at the end and hasn't a replicate with CLOSED (like the last two observations from above), keep them as is

 

By the end, I need Variable1 to include only the following observations:

123456789CLOSED

123456-PCLOSED

123456CLOSED

987654321

25648985

 

Thanks

Altijani

  

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

So you have a BASEID which sometimes is terminated with "CLOSE".  If it is you want to keep that record.  Otherwise you want to keep one copy of the ID without "CLOSED":

 

data have;
  input mon :$3.  id :$20.;
datalines;
Jan        123456789
Feb        123456789
Mar        123456789
Jan         123456-P
Jan         123456
Feb        123456-PCLOSED
Jan        123456CLOSED
Jan        987654321
Feb       123456789CLOSED
Jan        25648985
Mar       123456789CLOSED
Apr        123456789CLOSED
run;

data vneed / view=vneed;
  set have;
  baseid=tranwrd(id,"CLOSED","");
run;

proc sort data=vneed out=need;
  by baseid id; 
run;

data want (keep=id);
  set need;
  by baseid;
  if last.baseid;
run;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

This

each observation ending with CLOSED to be following the observation without CLOSED

does not match the first two lines of your "want" data.

altijani
Quartz | Level 8

Hi,

I am not sure I understand your question. I will create another data in excel and will post soon.

Thanks,

Altijani

 

mkeintz
PROC Star

So you have a BASEID which sometimes is terminated with "CLOSE".  If it is you want to keep that record.  Otherwise you want to keep one copy of the ID without "CLOSED":

 

data have;
  input mon :$3.  id :$20.;
datalines;
Jan        123456789
Feb        123456789
Mar        123456789
Jan         123456-P
Jan         123456
Feb        123456-PCLOSED
Jan        123456CLOSED
Jan        987654321
Feb       123456789CLOSED
Jan        25648985
Mar       123456789CLOSED
Apr        123456789CLOSED
run;

data vneed / view=vneed;
  set have;
  baseid=tranwrd(id,"CLOSED","");
run;

proc sort data=vneed out=need;
  by baseid id; 
run;

data want (keep=id);
  set need;
  by baseid;
  if last.baseid;
run;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
data have;
  input mon :$3.  id :$20.;
datalines;
Jan        123456789
Feb        123456789
Mar        123456789
Jan         123456-P
Jan         123456
Feb        123456-PCLOSED
Jan        123456CLOSED
Jan        987654321
Feb       123456789CLOSED
Jan        25648985
Mar       123456789CLOSED
Apr        123456789CLOSED
run;
data a b;
 set have;
 if find(id,'CLOSED') then output b;
  else output a;
run;
data a;
 set a;
 n+1;
run;
data b;
 set b;
 n+1;
run;
data want;
 set a b;
 by n;
run;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 734 views
  • 1 like
  • 4 in conversation