Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

fill missing variables by cycle

fill missing variables by cycle

fill missing variables by cycle

Hi all,

I am on this for some time now and don't know how to solve it.
It is actually only a small part of a much bigger task, but I am stuck and cannot go further.

I have quite a large dataset (ds) with around 40 variables.
To every ID there are multiple cycles. In every cycle there are multiple parameters with date and day (derived from date).
I have extracted the day for each group of parameters (in my example H* and F*) and written in separate variables [h_day and f_day].
Now it gets tricky (at least for me):
Next step is to fill the missings of both variables per cycle.

Attached is a snapshot of some mock-up data. There you can see (in a very minimal way) the initial dataset I am using, what I get using "retain" and how it should look in the end.
Also not ideal is the sorting and merging. If I use "id cycle" or "id cycle parameter" that is not unique. However I can get rid of duplicates later on.

Any help is appreciated as I am just starting to code.

snapshot at:
Thanks in advance,

RE: fill missing variables by cycle

I don't get the rule for filling the f* h* columns. With knowing that and knowing how to reverse order (or using a counter _n_ variable to sort reverse), it shouldn't be hard to retain the values to fill any missing. Can you let us known the rules?

RE: fill missing variables by cycle

From what I can make out the H_day and F_day columns refer to:

i) The cycle ie - A or B
ii) The Parameter ie H1 - F1 of cycle A

Therefore I as an example I guess:

If we use cycle A and Parameter H1

H_day = orginal_day and F_day = F1 of the same cycle.

But we'll need gingerfish to confirm...

RE: fill missing variables by cycle

Hi all,

I solved it! big smile

what I did is the following:


proc sort data=x1;
   by id cycle h_day;
data x2 (drop = _fd);
   set x1;
   retain _fd;
   if not missing(f_day) then  _fd = f_day ;
   else f_day = _fd ;
proc sort data = x2;
   by id cycle descending h_day;
data x3 (drop = _hd);
   set x2;
   retain _hd;
   if not missing(h_day) then  _hd = h_day ;
   else h_day = _hd ;
proc sort data = x3;
   by id date cycle;
So I had to do two steps in stead of one, because I needed two kinds of sorting to fill the missings correctly per cycle.

I am sorry I did not explain it in detail in the first place. I was looking at that problem for hours and didn't see anything anymore ...

@jj72uk: yes, you were right. It is sorted by cycle.

Thanks very much, it really is appreciated!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close