Set a rule where never a value different from 0 or missing appears

[NewUsrStat]

Hi guys,

suppose to have the following: 

data DB;
  input ID :$20. Var1: $20. Value1 Value2;
cards;
0001 Mydate1  .   .
0001 Mydate2 3.4 3.4 
0002 Mydate1  .   .
0002 Mydate2  3   3
0003 Mydate1  .   .
0003 Mydate2  .   .
0003 Mydate3  .   .
0003 Mydate4  .   .
0003 Mydate5  0  3.4
run;

I would say the following: 

if for each ID value 1 is always missing (for every Mydate*) or 0 (i.e., never a number different from 0 or not missing) then where Value1 = 0 then Value1 = Value2. 

Desired output: 

data DB1;
  input ID :$20. Var1: $20. Value1 Value2;
cards;
0001 Mydate1  .   .
0001 Mydate2 3.4 3.4 
0002 Mydate1  .   .
0002 Mydate2  3   3
0003 Mydate1  .   .
0003 Mydate2  .   .
0003 Mydate3  .   .
0003 Mydate4  .   .
0003 Mydate5  3.4  3.4
run;

Can you help me please?  

[PaigeMiller]

---

@NewUsrStat wrote:

if for each ID value 1 is always missing (for every Mydate*) or 0 (i.e., never a number different from 0 or not missing) then where Value1 = 0 then Value1 = Value2. 

---

Please explain this further, I do not understand. Please show us the desired output.

[NewUsrStat]

Just edited. What I don't know how to say is: when there is never a number (^=0) and conversely when there are always missing or 0s then...do something

[FreelanceReinh · Accepted solution]

Hi @NewUsrStat,

You can use a double DOW loop:

data want(drop=flag);
do until(last.ID);
  set DB;
  by ID;
  if value1 then flag=1;
end;
do until(last.ID);
  set DB;
  by ID;
  if ~flag & value1=0 then value1=value2;
  output;
end;
run;

In the first loop a flag variable is set to 1 if VALUE1 is neither zero nor missing for at least one observation in the current ID BY-group. (Note that the IF condition VALUE1 is true exactly if VALUE1 is neither zero nor missing.) The second loop performs the desired change of VALUE1 if the flag was not set to 1 and writes all observations of the ID to dataset WANT.

[Tom]

Your examples do not seem sufficient to test all of the implications of your requested logic.

Is it always the case that you want to replace VALUE1 with the current value of VALUE2?  What if the current value of VALUE2 is 0 also?  Do you want VALUE1 to stay as 0?  What if the current values of VALUE2 is missing? Should VALUE1 be set to missing also?

NOTE: It would be much easier to talk about your examples if you named the variables something else, perhaps VAR1 and VAR2, so that you don't confuse when you are talking about the current value of the variable or the variable itself.

Also could the values ever by negative?  It not then you are asking if the MAX() value is greater than zero or not.  Which makes PROC SQL a likely way to generate what you want since it can re-merge aggregate function results, like MAX(), back onto all of the observations for the BY group.

In all of your examples I keep wondering why it is that the values are attached to the wrong observations to begin with?  Is there something amiss in the way you are building these datasets to begin with?  Could fixing that process get the values onto the right observations so they no longer need to be corrected (or "moved")?