topic Re: How do I retain value in missing observations in SAS Programming

How do I retain value in missing observations

nans_p — Sat, 03 Apr 2021 23:01:06 GMT

Hi,
I can’t get my head around to fix this piece of code, my data looks somewhat like this with 4 variable
Data have;
Subjid collapse count seq
101 1
101 1 1 2
101 1 1 3
101 4
101 5
101 1 1 6
101 7

I want to retain the variable collapse and count at missing seq 4 &5.
For e.g I want my dataset to look like:

Data have;
Subjid collapse count seq
101 1
101 1 1 2
101 1 1 3
101 1 1 4
101 1 1 5
101 1 1 6
101 7

Please let me know how I can fix this, I cannot use the seq variable to code because the situation can be different for different subjids.

Thanks in advance

Re: How do I retain value in missing observations

Patrick — Sat, 03 Apr 2021 23:28:26 GMT

Please explain the required logic a bit more in detail - especially why seq remains missing for the first and last obs in your sample data.

Re: How do I retain value in missing observations

mkeintz — Sun, 04 Apr 2021 03:30:32 GMT

I think you need to make a working data step to show the initial condition of your data. It appears that SEQ is missing for obs 1 and 7. Instead you have collapse=1 for the first obs and collapse=7 for the last.

But it that is not your intention, then you should show missing values for collapse and count (two .'s between subjid and seq).

If I am correct (this is untested until in absence of actual startup data in a data step), then this code should work.

data want (drop=_:);
  set have;
  retain _last_collapse _last_count;

  if collapse^=. then _last_collapse=collapse;
  if count^=.    then _last_count=count;

  if collapse=.  then collapse=_last_collapse;
  if count=.     then count=_last_count;
run;

Re: How do I retain value in missing observations

nans_p — Sun, 04 Apr 2021 08:07:31 GMT

@Patrick @mkeintz

Sorry for not being so specific - below is a clear explanation. Data Have is how my dataset looks like and Data want is how I want my data too look like.

Because I have collapsed a record highlighted in green, I want to flag all the associated date records which fall from the 02-Dec-17 to 16-Jan-18. My question is how do I retain the collapsed and the count variable at observation 5&6 of the seq variable.

Re: How do I retain value in missing observations

Kurt_Bremser — Sun, 04 Apr 2021 11:14:26 GMT

So you simply want to carry forward the last non-missing observation?

data want;
set have;
by subjid;
retain _coll _cnt;
if first.subjid
then do;
  _coll = .;
  _cnt = .;
end;
if collapsed ne .
then _coll = collapsed;
else collapsed = _coll;
if count ne .
then _cnt = count;
else count = _cnt;
drop _:;
run;

Untested, for lack of usable example data.

Re: How do I retain value in missing observations

mkeintz — Sun, 04 Apr 2021 14:20:31 GMT

So you have now given us some more information - namely about the position of SEQ.

You have NOT stated why collapsed and count are propagated from obs 4 through 5 and 6, but obs 7 is not propagated. Is it because ex='Y' in obs 4, but not in obs 7?

When does propagation stop? In the above case, obs 7 has data that stops propagation of data from obs 4, but what if there were no such data after obs 4? When would propagation stop, if at all?

Also you show only only one subjid in your data. You didn't say whether data should be propagated across subjects (probably not, but it would be good to have it stated).

In short, help us help you - by providing a sufficient description of the problem such that you won't have to answer lots of follow-up questions. That's probably a more valuable skill to develop than sas coding, even in this forum.

Re: How do I retain value in missing observations

nans_p — Sun, 11 Apr 2021 10:34:22 GMT

@Patrick @Kurt_Bremser

Hi,

Any idea how I can get the missing observation where highlighted in seq variable 5&6, but it should not retain the values in seq 8 and 9 of my orignal post.

I tried to use retain statement but it does not provide the output I want.

I am sharing another example below - I only want collap, count, collapse where highlighted in yellow. The reason behind is because I have collapsed dates where collapex =Y . All the dates associated with the collapsing should have collap = 1, count =1 or count =2 if a second collapsing of date has happened within same subject and collapse = Y.

Hope this is clear. Apart from retain what is the best method I can use?

Re: How do I retain value in missing observations

Kurt_Bremser — Sun, 11 Apr 2021 13:42:01 GMT

Please post your data in usable form, which means a data step with datalines, which we can copy/paste to our environment and submit.

Test your data step code before posting to make sure it recreates a sufficient example of your data, and use the "little running man" button to open a code box into which you paste the code.