topic Re: SAS MACRO - How to convert character variables to date variables in SAS Programming

SAS MACRO - How to convert character variables to date variables

rchung — Fri, 06 Jan 2023 22:06:54 GMT

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

Re: SAS MACRO - How to convert character variables to date variables

Astounding — Fri, 06 Jan 2023 22:16:43 GMT

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.

Re: SAS MACRO - How to convert character variables to date variables

ballardw — Fri, 06 Jan 2023 22:20:09 GMT

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

Re: SAS MACRO - How to convert character variables to date variables

Tom — Fri, 06 Jan 2023 22:23:06 GMT

You are looking for COUNTW().

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

Re: SAS MACRO - How to convert character variables to date variables

Astounding — Fri, 06 Jan 2023 22:29:29 GMT

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

Re: SAS MACRO - How to convert character variables to date variables

ErikLund_Jensen — Sat, 07 Jan 2023 12:21:31 GMT

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;

Re: SAS MACRO - How to convert character variables to date variables

rchung — Mon, 09 Jan 2023 17:25:03 GMT

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

Re: SAS MACRO - How to convert character variables to date variables

rchung — Mon, 09 Jan 2023 17:26:49 GMT

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

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

Re: SAS MACRO - How to convert character variables to date variables

rchung — Mon, 09 Jan 2023 17:31:53 GMT

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

Re: SAS MACRO - How to convert character variables to date variables

Tom — Mon, 09 Jan 2023 17:39:44 GMT

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.

Re: SAS MACRO - How to convert character variables to date variables

rchung — Mon, 09 Jan 2023 17:44:18 GMT

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. 🙂

Re: SAS MACRO - How to convert character variables to date variables

rchung — Mon, 09 Jan 2023 17:59:24 GMT

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!