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

Hello, 

 

I have day or month is unknown and listed as UN and UNK respectively. 

 

SAS Dataset: 

data TEST;
   input date_have $ 1-20;
   datalines;
00JAN2020
UNJAN2020
UNUNK2020
UNJUN2020
01JUN2020
;
run;

data CLEAN ;
   	set TEST;
  	C = 'UN'; *<- leading character(s) to be removed;
 	D = "UNK";
 	E = "00";
   	P1 = verify(date_have,C);
   
   	if P1 then date_want = substr(date_have, P1);    
         else date_want = '';
 
 	P1 = verify(date_want,D); 
   	if P1 then date_want = substr(date_want, P1);    
         else date_want = '';
 
 	P1 = verify(date_want,E); 
   	if P1 then date_want = substr(date_want, P1);    
         else date_want = '';
     if P1=2 then date_want = date_have;    
    drop c d e;
run;

I want to remove 00 or UN or UNK. I do not want to remove UN from month JUN. By using my code, I am removing leading zero (0) from date 

 

Above code is working but I am requesting to get something simple code for desire output. 

 

dht115_0-1648474158690.png

 



1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data TEST;
   input date_have $ 1-20;
   datalines;
00JAN2020
UNJAN2020
UNUNK2020
UNJUN2020
01JUN2020
;
run;

data want;
 set test;
want=prxchange('s/^UN|^00|UNK//i',-1,date_have);
run;

View solution in original post

3 REPLIES 3
mkeintz
PROC Star

You can make use of the "=:" relation operator, and the TRNSTRN function:

 

data TEST;
   input date_have $ 1-20;
   datalines;
00JAN2020
UNJAN2020
UNUNK2020
UNJUN2020
01JUN2020
;
run;

data want;
  set test;
  if date_have =: 'UN' or date_have =: '00' then date_want=substr(date_have,3); 
  else  date_want=date_have;
  date_want=transtrn(date_want,'UNK','');
  put (_all_) (=);
run;

The '=:' operator compares only the initial characters.  The comparison will examine only as many characters as the shorter of the two comparison values.  So in the above, there is a check for leading "UN"  and leading "00".   The TRANSTRN removes "UNK" from the value regardless of whether it is the leading character string, or an interior character string.

 

Note this assumes that your DATE_HAVE always leads with a day-of-month value, or "UN", or "00".   I.e. none of your DATE_HAVE values start with "UNK"  (as in UNK2020) - although that could be trivially addressed.

--------------------------
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 TEST;
   input date_have $ 1-20;
   datalines;
00JAN2020
UNJAN2020
UNUNK2020
UNJUN2020
01JUN2020
;
run;

data want;
 set test;
want=prxchange('s/^UN|^00|UNK//i',-1,date_have);
run;
dht115
Calcite | Level 5

Hello, 

 

How to compare partial date with actual date in sas.

 

data TEST;
   input date_have $ 9. date_compare $ 9.;
   datalines;
00JAN2020 10NOV2020
UNJAN2020 05DEC2020
UNUNK2020 16DEC2019
UNJUN2020 14FEB2021
01JAN2020 01MAY2020
20JUN2020 01JAN2020
;
run;

DATA TEST1;
SET TEST;
/* DELETE 00, UN, UNK BY USING PRXCHANGE FUNCTION */
NEW_have=prxchange('s/^UN|^00|UNK//i',-1,date_have);

RUN;

I need to compare new_have variable to date_compare variable. 

 

  • new_have <=date_compare

When I run above code, I need to remove Observation 3 and Observation 6.

Observation 3 - compare date is in 2019 and our original date is in 2020. 

Observation 6 - compare date is in JAN2020 and our original date is in JUN2020. 

 

dht115_1-1648588240148.png

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 412 views
  • 0 likes
  • 3 in conversation