SAS MACRO - How to convert character variables to date variables

[rchung]

Hello.

I need to convert 26 character variables (type=Char, len=10 and format=$10.), named var_a, var_b, var_c, ... , var_z. Their values all look like this: "2022-12-31" 

I need to convert them to date variables without changing their names. In other words, I need the same variables in the same names but of different attributes: type=Num, len=8 and format=YYMMDD10.

What would be the best way to accomplish this?

Thank you so much!

Rakkoo

[Astounding]

Given that you want to use the same names as you already have, you will need to jump through a hoop or two.  Here's the logic that you would need to repeat for all 26 variabes:

temp_a = input(var_a, yymmdd10.);
drop var_a;
rename temp_a = var_a;

Here's how you would add in macro language to carry out the calculations for multiple variables.  The example will use 3 variables, but you can add to the list.

%macro convert (suffix_list=);
   %local suffix n;
   %do n = 1 to %sysfunc(count(&suffix_list));
      %let suffix = %scan(&suffix_list, &n);
      temp_&suffix = input(var_&suffix, yymmdd10.);
      drop var_&suffix;
      rename temp_&suffix = var_&suffix;
   %end;
%mend convert;

data want;
   set have;
   %convert (suffix_list = a b c)
run;

Sorry, COUNT is the wrong function.  I will look it up and find the right one.

[Tom]

You are looking for COUNTW().

%do n = 1 to %sysfunc(countw(&suffix_list,%str( )));

[Astounding]

Thanks, Tom.  Yes, COUNTW is correct not COUNT.

[rchung]

Thank you! BTW, I had to remove the "%str()" part:

%do n = 1 to %sysfunc(countw(&suffix_list));

[Tom]

If you remove the %STR( ) part (note the space character) then you are asking COUNTW() to use ANY of the multiple default characters ( blank ! $ % & ( ) * + , - . / ; < ^ | )  that it uses to delimit the words instead of just the space character.  Depending on the content of the "words" that could lead to an overcount.  

[rchung]

Now I see what I did wrong: I used %STR() instead of %STR( ). I missed a space in the parenthesis! Your suggestion works! Thank you!

[rchung]

Hi Astounding,

Your macro works beautifully. But I cannot format those date variables in the same data step. I also failed to include formats in the macro... Now, their frequencies show "169632" instead of "2013-10-01" 😁

Anyway, I like your macro. I have learned a lot from you.

Thank you!

Rakkoo

[ballardw · Accepted solution]

Normally I would say the best way is to do this when the data is read into SAS using a proper informat.

Are these data step variables?

If so the type cannot be changed, you need to do a rename and create new variables in a different data set of the desired type if you want to use the same name. Something like this:

data want;
   set have (rename=(var_a=oldvar_a var_b=oldvar_b var_c= oldvar_c);
   array old (*) oldvar_a oldvar_b oldvar_c;
   array new(*) var_a var_b var_c ;
   do i=1 to dim(old);
       new[i] = input(old[i],yymmdd10.);
   end;
   format var_a var_b var_c yymmdd10.;
   drop i;
run;

I only typed out 3 variables, you would need add yours in the obvious place. The Rename here is used as a data set option and is applied as the data is read from an existing data set so you can use the changed name in the data step.

Note: if you have a chance when naming variables with anything resembling sequences SAS will work much better with numeric suffixes of 1, 2, 3 instead of A, B, C as there are many places that SAS will use sequenced lists including in the Rename statement or data set option.

data example;
   input x1 x2 x3;
datalines;
1 2 3
;

data renamed;
   set example (rename=( x1-x3= newx1-newx3));
run;

---

@rchung wrote:

Hello.

I need to convert 26 character variables (type=Char, len=10 and format=$10.), named var_a, var_b, var_c, ... , var_z. Their values all look like this: "2022-12-31" 

I need to convert them to date variables without changing their names. In other words, I need the same variables in the same names but of different attributes: type=Num, len=8 and format=YYMMDD10.

What would be the best way to accomplish this?

Thank you so much!

Rakkoo

---

[rchung]

Thank you so much! It works perfect! The output is exactly what I need. 🙂

[ErikLund_Jensen]

Hi @rchung 

I find it easier to use a Proc SQL step in cases like this. You get full control over the output including variable order, and you don't need to bother with renaming or counting, so the code becomes simpler. The downside is that it is not dynamic. You have to write the full code, so you miss the fun of getting a complex piece of code to work, but you get the job done in a way that everybody can understand and maintain.

data have;
  ID = 1;
  Var_a = '2020-04-30';
  Var_b = '2021-08-31';
  Var_c = '2022-12-31';
run;

proc sql;
  create table want as
    select
      ID,
      input(Var_a,yymmdd10.) as Var_a format=YYMMDD10.,
      input(Var_b,yymmdd10.) as Var_b format=YYMMDD10.,
      input(Var_c,yymmdd10.) as Var_c format=YYMMDD10.
    from have;
quit;

[rchung]

It is great to know that proc sql can simply the dropping-and-renaming part so much! This is such a useful tip for me. But I do not accept it as the solution, because it is like a two-step solution: (1) Use proc sql to convert variables to dates, and (2) combine these converted variables back into the original datafile. Anyway, I like this tip so much. Very useful! Thank you. 🙂