Array-Apply on multiple columns

[Ronein]

Hello

I have a numeric date and I want to convert it to char date with structure DD/MM/YYYY.

If the numeric date=0 then I want to get null value without dot.

This step is done 100% and all is good.

Then ,I want to apply this logic on multiple columns .

I want to apply it on all columns in data set .

/****Apply on one column*********/
/****Apply on one column*********/
/****Apply on one column*********/
data have;
input YYYYMMDD ;
cards;
20230327
20230324
0
0
20230322
;
Run;
data want(KEEP= YYYYMMDD want_date);
set have;
/**Convert from numeric date to sas date**/
IF YYYYMMDD=0 then date_SAS=.;
else date_SAS=input(compress(put(YYYYMMDD,best.)),yymmdd8.);
/***Convert from numeric date to char date***/
char_date=put(date_SAS,DDMMYY10.);
IF compress(char_date) ne '.' then  want_date=compress(char_date);
Run;



/****Apply on multiple columns-problem*********/
/****Apply on multiple columns-problem*********/
/****Apply on multiple columns-problem*********/
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;

May anyone show the code how to do it please? 

[yabwon]

Try this:

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 want2(KEEP= YYYYMMDD1 date2 date X4 want_date:);
set have2;
array YYYYMMDD YYYYMMDD1 date2 date X4;
array want_date $ 10 want_date1-want_date4;

do over YYYYMMDD;

/**Convert from numeric date to sas date**/
IF YYYYMMDD=0 then date_SAS=.;
else date_SAS=input(compress(put(YYYYMMDD,best.)),yymmdd8.);
/***Convert from numeric date to char date***/
char_date=put(date_SAS,DDMMYY10.);
IF compress(char_date) ne '.' then  want_date=compress(char_date);

end;

Run;

[EDIT] Reading about DO OVER: https://support.sas.com/resources/papers/proceedings10/158-2010.pdf

Bart

[PaigeMiller]

While DO OVER still works, it is now undocumented and deprecated.

https://communities.sas.com/t5/SAS-Programming/quot-DO-OVER-quot-is-Undocumented-for-SAS-9-4-Right/m...

According to @ballardw:

"Do Over" was pretty much deprecated in SAS 6, I believe. SAS tries to make sure that prior code will run so no errors and will work in existing code. Not documenting a bit of code is one way of saying "it works now but you never know when it might stop and probably should not be using it."

So I would advise against using DO OVER, despite the article you link to.

In the code from @Ronein, COMPRESS() is not needed, seems unnecessary for text strings that are all integers. This works without COMPRESS (although the idea of the final output being character string dates makes me cringe, dates should be numbers)

data want;
	set have;
	/**Convert from numeric date to sas date**/
	date_sas=input(put(yyyymmdd, best8.), yymmdd8.);
	/***Convert from numeric date to char date***/
	if not missing(date_sas) then want_date=put(date_sas, ddmmyy10.);
	drop date_sas;
run;

And then the array as shown by @yabwon can be implemented on this simpler code (but really, leave dates as numeric variables, not character strings).

[ErikLund_Jensen]

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!