topic Re: How to eliminate "UN" ( Month / date) from given date data in SAS Programming

How to eliminate "UN" ( Month / date) from given date data

pdhokriya — Sat, 29 Aug 2020 18:24:30 GMT

Hi Reader,

My requirement is &output_date . I have created below program but no satisfied and still I want to cut short this macro.

Kindly suggest me more dynamic way for below mention senerio to get &output_date. Or there any other approch which I can do it.

data un_date ;
input date1$ 10.;
CARDS;
2001-UN-UN
2002-12-UN
2006-11-UN
2004-UN-02
2001
;
run;

****My pr0gram*****

%macro offset_date(date1, output_date, indset , outdset);
data &outdset ;
set &indset ;
if &date1 ne "" then do ;
if index(&date1,"UN")=0 then &output_date=&date1;
else if index(&date1,"UN")> 0 then do;
if scan(&date1,2,"-") eq "UN" and scan(&date1,3,"-") ne "UN" then &output_date=tranwrd(&date1,"-UN-","----");
if scan(&date1,2,"-") ne "UN" and scan(&date1,3,"-") eq "UN" then &output_date=tranwrd(&date1,"-UN","");
if scan(&date1,2,"-") eq "UN" and scan(&date1,3,"-") eq "UN" then &output_date=tranwrd(&date1,"-UN-UN","");
end;
end;
run;
%mend offset_date;
%offset_date(date1, output_date, un_date, un_data);

Re: How to eliminate "UN" ( Month / date) from given date data

mkeintz — Sat, 29 Aug 2020 19:34:52 GMT

You're telling us that your code is not satisfactory, yet you don't tell us what you want the results to look like. Please provide the desired values for each of your starting values.

Below is just a guess on my part. But you would probably get more, and better, responses is you showed explicit desired results. Help us help you.

data un_date ;
input &date1$ 10.;
CARDS;
2001-UN-UN
2002-12-UN
2006-11-UN
2004-UN-02
2001
;
run;
%let date1=date1;
%let output_date=date2;
data want;
  set un_date;
  &output_date=&date1 ;
  if index(&date1,'UN')>0 then do;
    &output_date=tranwrd(&output_date,'-UN-UN','');    
    &output_date=tranwrd(&output_date,'-UN-','----'); 
    &output_date=tranwrd(&output_date,'-UN','');      
  end;
run;

You can put this code into a macro, instead my open code solution.

I think you should bother with scanning for specific pattern combinations. Instead transform in this specific sequence: the double UN first, then the trailing UN, and the internal UN. There will be no need for if tests since whichever of these conditions is discovered first, subsequent conditions will not arise.

Re: How to eliminate "UN" ( Month / date) from given date data

pdhokriya — Sun, 30 Aug 2020 08:34:50 GMT

Thank you so much for the suggested solution.

Can you share the syntax in Proc sql too.

Re: How to eliminate "UN" ( Month / date) from given date data

mkeintz — Sun, 30 Aug 2020 13:47:25 GMT

@pdhokriya wrote:
Thank you so much for the suggested solution.

Can you share the syntax in Proc sql too.

I leave that to you. But here's a data step program that you should be able to replicate in SQL using the CASE expression:

data want;
  set un_date;
  if index(&date1,'-UN-UN') then &output_date=tranwrd(&date1,'-UN-UN','');    else
  if index(&date1,'-UN-')   then &output_date=tranwrd(&date1,'-UN-','----');  else
  &output_date=tranwrd(&date1,'-UN','');      
run;

Re: How to eliminate "UN" ( Month / date) from given date data

ChrisNZ — Mon, 31 Aug 2020 08:33:06 GMT

Another way

data WANT ;
  input DATE1 $10.; 
  DATE2 = prxchange( 's/-UN//', 2, prxchange( 's/(\d-)UN(-\d)/\1--\2/', 1, DATE1 ) );
cards;
2001-UN-UN
2002-12-UN
2006-11-UN
2004-UN-02
2001
run;

Obs	DATE1	DATE2
1	2001-UN-UN	2001
2	2002-12-UN	2002-12
3	2006-11-UN	2006-11
4	2004-UN-02	2004----02
5	2001	2001

Re: How to eliminate "UN" ( Month / date) from given date data

pdhokriya — Wed, 02 Sep 2020 16:34:42 GMT

So more than 1 date how can I handle ?
data WANT ;
input cmstdat $10. cmendat $11.;
cards;
2001-UN-UN 2001-UN-01
2002-12-UN 2001
2006-11-UN 2001-01-UN
2004-UN-02 2001-UN-02
2008-03-03
2001

;
run;

%let input_date=cmstdat;
%let output_date=cmstdtc;

data example3;
set want;
&output_date=&input_date ;
if index(&input_date,'UN')>0 then do;
&output_date=tranwrd(&output_date,'-UN-UN','');
&output_date=tranwrd(&output_date,'-UN-','----');
&output_date=tranwrd(&output_date,'-UN','');
end;
run;

So Can I use %macro; %mend?

Re: How to eliminate "UN" ( Month / date) from given date data

Tom — Wed, 02 Sep 2020 16:47:03 GMT

I am curious. What is the meaning of a value like 2004-UN-02? Is that supposed to indicate that it was the second day of the month but they can't remember which month? How much values does the 02 actually add in that case?

Re: How to eliminate "UN" ( Month / date) from given date data

pdhokriya — Fri, 04 Sep 2020 05:11:44 GMT

I am not sure about your question but still for your understanding , ----------------------------yyyy-mm-dd format so if month is missing we generally cosider it UN in raw data , targeted data we consider it as "--" so in future it can be 01 to 12 month.

Hope you get it.

Re: How to eliminate "UN" ( Month / date) from given date data

Tom — Sat, 05 Sep 2020 03:03:47 GMT

I get the idea of translating UN to hyphens.

My question why would you keep the day of the month when the actual month is not present. How can you have any confidence in the day of the month provided if the month itself is not known? Perhaps it is even more likely that they meant the month number and not the day number.