BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sma11s101
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

2 REPLIES 2
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
sma11s101
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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