Hi @Ronein
The conversion can be done i one statement with the same set of functions. There is no need for intermediate variables and if-then conditions. Note the leading ?? in the yymmdd8 format. The result is set to missing if an invalid date value (incl 0) is encountered, but all the notes "Invalid argument to function INPUT at line ..." are suppressed.
data have;
input YYYYMMDD ;
cards;
20230327
20230324
0
0
20230322
;
run;
data want;
set have;
want_date=compress(put(input(put(YYYYMMDD,8.),??yymmdd8.),DDMMYY10.),'.');
run;
When it comes to make this dynamic, so it can handle an unknown set of input variables, there is a problem with naming the output variables, because it is difficult to create new variables in a data step with names derived from unknown input variables, not just wanted_1-wanted_n.
In this example, the original variables are dropped, because there is no easy way to drop them afterwards without writing them as literals in a data step. The original variable names are reused for the new character variables in output, so it has the same number of variables with the same names and order, but with different type and content.
I started out with building variable lists in macro variables, but it became rather big and complicated, so I changed to a call execute-approach. Using a data step to write another data step this way is a very powerful tool, as you can see in the following example:
data have2;
input YYYYMMDD1 1-8 date2 10-18 date 19-27 X4 ;
cards;
20230327 20230327 20230322 20230322
20230324 0 20230322 20230322
0 20230322 0 0
0 0 0 0
20230322 20230322 0 20230322
;
Run;
data _null_;
set have2 (obs=1);
array numdate{*} _numeric_;
length ilist vlist $200;*48.;
do i = 1 to dim(numdate);
ilist = catx(' ', ilist, vname(numdate{i}));
vlist = catx(' ', vlist, catt('want_', vname(numdate{i})));
end;
call execute(catx(' ', 'data want2 (drop=i', ilist, '); set have2;'));
call execute('array numdate{*} _numeric_;');
call execute(catx(' ', 'array chardate {*} $10', vlist, ';'));
call execute('do i = 1 to dim(numdate);');
call execute('chardate{i} = compress(put(input(put(numdate{i},8.),??yymmdd8.),DDMMYY10.),".");');
call execute('end; run;');
run;
I had a lot of fun out of this, thank you!
... View more
