relegating the levels of a variable when both conditions do not meet

[BeatriceWang]

Happy Thursday Everyone!

Nice to e-meet you all.

I have two conditions to define the level of a variable (please see below screenshot). What I want is when both conditions for var2 and var3 do not meet the level of var4 will go down one level, for example, from level 5 to level 4 and so on. 

Below is how I define the level of var4:

However from the output the display does not show what I want (please see below screen shot).

I would very much appreciate if you can advise how I can change my SAS code so that I can achieve what I want to display and apologize if the answer is obvious.

Best,

Beatrice

[Quentin]

Hi,

I don't see any obvious errors in your code, but it certainly may not be doing what you intend.

It's hard to interpret the output you showed.  One way to check this sort of algorithm is to use PROC FREQ to make a "Check Table" that shows the values of each variable.  So you could run:

proc freq data=have;
  tables var1*var2*var3*var4/missing list;
run;

If you have many unique values, you can use a format to limit the number of rows in the table (e.g. format all the variables as integers).

A table like that is very helpful id de-bugging this sort of situation.

[BeatriceWang]

Hi Quentin,

Thank you for your reply.

Your suggestion for bebugging and your sas code are helpful.

Regards,

Beatrice

[ballardw]

Please post code as TEXT, preferably in a text box opened with the </> icon above the message window. Copy from your editor, open the box and paste the text.

Show what you actually expect as a result of the data manipulation, best is if you provide example data as a data step, again pasted into a text box, as we cannot write code to read a picture. And show the entire data step. Issues might arise with other code than the bit you think it does.

Your "output" is basically useless as we do not know how you made it or what the input for that actually was, much less what the input should be.

Since you do not state in words what the conditions are in words it is somewhat difficult to actually see if you code meets those conditions.

[FreelanceReinh · Accepted solution]

Hello @BeatriceWang,

These large values are very well consistent with your IF conditions. Note that they specify unconditional lower bounds for VAR2 and VAR3, but only conditional upper bounds. For example, an observation with VAR4=3 must satisfy the condition VAR2>=24 and VAR3>=6 -- these are the lower bounds. But for the next level VAR4=4 both conditions VAR2>=68 and VAR3>=17 must be met. So, if only one of these two conditions is met, VAR4 is definitely not 4, but can still be 3 although the other condition is met. In other words, theoretically VAR2 alone can be arbitrarily large, regardless of VAR4, and the same is true for VAR3 alone.

If you sketch the areas for the VAR4 values in an x-y coordinate system (with x=VAR2 and y=VAR3), you'll see this immediately:

[BeatriceWang]

Hi FreelanceReinhard,

Thank you for your explanation. Yes. you are right.

The requirement is if both conditions for var2 and var3 are not met the var4 will be assigned to one level down.

Are you able to suggest how I can change my sas code to meet the requirement and display correctly?

Regards,

Beatrice

[FreelanceReinh]

So you mean: Only if both conditions are not met, var4 gets the next lower value?

if n(var2, var3)<2           then var4=.;
else if var2< 12 and var3< 3 then var4=1;
else if var2< 24 and var3< 6 then var4=2;
else if var2< 68 and var3<17 then var4=3;
else if var2<128 and var3<32 then var4=4;
else                              var4=5;

[BeatriceWang]

Hi again Freelance,

Thank you for your further response.

No, I still need the original conditions for each level of var4 but if both original conditions are not met the record needs to be assigned to be one level down.

Any further comments or do I need to change the grouping?

Best,

Beatrice

[FreelanceReinh]

---

@BeatriceWang wrote:

(...) if both original conditions are not met the record needs to be assigned to be one level down.

---

Interestingly, this particular requirement is met by both sets of criteria which I depicted in my earlier posts, isn't it?

I'm not sure I understand why you say "one level down" -- as if there was some original level of VAR4 that is changed under certain conditions. Do your criteria uniquely determine the value of VAR4, given the values of VAR2 and VAR3 (and VAR1)? If so, please enter the desired values for VAR4 into a table like this:

	0<=var2<12	12<=var2<24	24<=var2<68	68<=var2<128	var2>=128
var3>=32					5
17<=var3<32
6<=var3<17
3<=var3<6
0<=var3<3

(I think the "5" in the upper right corner is consensus.)

Assuming non-missing, non-negative values for var2 and var3, the above table covers all possible combinations. Feel free to change, e.g., "<=" to "<" and vice versa (in a consistent manner), if needed.

Otherwise, please explain further.

[BeatriceWang]

Hi Freelance,

Sorry I have been very busy and have been unable to get back to you quickly. Also sorry some of my descriptions may have confused you.

Thank you so much for all your responses. All your suggestions are very helpful.

I have talked to my project team. The requirement will make the upper bound of the var2 arbitrarily large. We need to change the grouping criteria.

Thanks again,

Beatrice