Help using Base SAS procedures

Expand drops character variables?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Expand drops character variables?

I am trying to expand some data by the second to fill missing values. However, the input data has a couple of character variables that do not survive the procedure.  It drops everything but the numeric variables.  I would like to keep the character variables using the same step method.

Any suggestions would be greatly appreciated.

          proc expand data=input out=output to=second method=step;

                id TIME;

          run;


Accepted Solutions
Solution
‎08-07-2012 09:21 AM
Trusted Advisor
Posts: 1,019

Re: Expand drops character variables?

Posted in reply to sma11s101

It makes sense that proc expand won't propagate character vars, since (unlike step) most methods intrinsically require a numeric value as input to the various SPLINE, JOIN, AGGREGATE methods.  You could recode the character values into numeric prior to EXPAND, but otherwise you'll have to run a DATA step merging INPUT character vars with OUTPUT.

Since you're using STEP, you probably want LOCF (last observation carried forward) for the character variables.  This should work (where CVAR1 CVAR2 are names of character variables ):

data want;

  merge output  input (keep=time cvar1 cvar2);
  by time;

  array cvars {*} $200  cvar1 cvar2;

  array locf {2} $200 _temporary_;

  do _N_=1 to dim(cvars);

    locf{_N_} = coalescec(cvars{_N_},locf{_N_});

    cvars{_N_} = locf{_N_};

  end;
run;

BTW, if you don't know in advance the number of variables in the CVARS, but it's never more than, say, 100, you could just declare the LOCF array to have 100 elements.  Also, this program doesn't require all the character vars to have the same length, but it does assume none are longer than 200 byters.

View solution in original post


All Replies
Solution
‎08-07-2012 09:21 AM
Trusted Advisor
Posts: 1,019

Re: Expand drops character variables?

Posted in reply to sma11s101

It makes sense that proc expand won't propagate character vars, since (unlike step) most methods intrinsically require a numeric value as input to the various SPLINE, JOIN, AGGREGATE methods.  You could recode the character values into numeric prior to EXPAND, but otherwise you'll have to run a DATA step merging INPUT character vars with OUTPUT.

Since you're using STEP, you probably want LOCF (last observation carried forward) for the character variables.  This should work (where CVAR1 CVAR2 are names of character variables ):

data want;

  merge output  input (keep=time cvar1 cvar2);
  by time;

  array cvars {*} $200  cvar1 cvar2;

  array locf {2} $200 _temporary_;

  do _N_=1 to dim(cvars);

    locf{_N_} = coalescec(cvars{_N_},locf{_N_});

    cvars{_N_} = locf{_N_};

  end;
run;

BTW, if you don't know in advance the number of variables in the CVARS, but it's never more than, say, 100, you could just declare the LOCF array to have 100 elements.  Also, this program doesn't require all the character vars to have the same length, but it does assume none are longer than 200 byters.

New Contributor
Posts: 2

Re: Expand drops character variables?

That worked perfectly!  Thank you so much for your help.

For future reference if anyone else uses this, the 2 in locf{2} should be changed to match the number of cvars you have.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 647 views
  • 0 likes
  • 2 in conversation