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);
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.
@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;
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?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: