topic Re: Case Statement Error in Flagging in SAS Programming

Case Statement Error in Flagging

Scottie_T — Fri, 26 Mar 2021 14:14:06 GMT

Hi,

I have the following case statement

case when (m.loc_no = m.Prod_Ctr_Num) or m.Prod_Ctr_Num = '7370'
then "N"
else "Y" end as Shares_Flag,

It should be read and output like this.

Loc_No=2102 = Prod_Ctr_Num = 2102 then "N" .... But the code is not reading this as a N and outputing a "Y"

They are both characters and both are formatted to the same digits. I am lost on why this statement can't read them as the same.

Re: Case Statement Error in Flagging

Tom — Fri, 26 Mar 2021 14:33:32 GMT

I don't understand what your question is. Also what did you mean by this:

Loc_No=2102 = Prod_Ctr_Num = 2102 then "N"

Re: Case Statement Error in Flagging

Scottie_T — Fri, 26 Mar 2021 14:46:48 GMT

It should read if Loc_No = Prod_Ctr_Num then "N"

In my case it should be reading

2102=2102 s a "N" but it is reading it as a "Y"

Re: Case Statement Error in Flagging

Tom — Fri, 26 Mar 2021 14:58:39 GMT

I don't understand your question. It looks like you told it that if the two variables have the same value then the result should be N.

What is is meaning of "2102=2102"? Are those supposed to be some example values of the variables? If so they look the same to me, but I thought you had character variables. In character variables leading spaces are important in comparison and trailing spaces are not. So this test is false:

'2102' = '   2102'

Or are you saying that even if the variables are equal but the value is "2102" the answer should be Y instead of N?

case
  when ( Loc_No = Prod_Ctr_Num  and Loc_no='2101') then 'Y'
  when ( Loc_No = Prod_Ctr_Num) then 'N'
  else '?'
end

Re: Case Statement Error in Flagging

ballardw — Fri, 26 Mar 2021 15:06:13 GMT

@Scottie_T wrote:

Hi,

I have the following case statement

case when (m.loc_no = m.Prod_Ctr_Num) or m.Prod_Ctr_Num = '7370'
then "N"
else "Y" end as Shares_Flag,

It should be read and output like this.

Loc_No=2102 = Prod_Ctr_Num = 2102 then "N" .... But the code is not reading this as a N and outputing a "Y"

They are both characters and both are formatted to the same digits. I am lost on why this statement can't read them as the same.

Formatted to the same number of characters, not "same digits". Format controls display, not content. If a variable has 10 characters but you assign a format of $4. it only displays 4 characters when printed but the full 10 characters would used for comparisons. You many want to consider examining the assigned Lengths of the variables.

With character variables an equality condition compares character by character and stops with "not equal" as soon as there is a difference. The longer value compares to a "missing" character and returns not equal for a comparison.

See this example:

data junk;
   x='2102';
   y='21024567';
   format x y $4.;
   put _all_;
   if x=y then put "The variables are equal";
   Else put y= $10.;
run;

Another possibility depending on how you are looking at your values is that there is leading space in one value not present in the other. The displays using Proc print will usually left justify the text effectively removing the "space" for human eyes but the value is different than what you may see.

Re: Case Statement Error in Flagging

PhilC — Fri, 26 Mar 2021 15:38:12 GMT

Please consult at a PROC CONTENTS readout, or equivalent, of your data and inform us if the variables are strings or numeric . I suspect that you are confusing string and numeric variables as well, but its hard to say without you stating.

Re: Case Statement Error in Flagging

Scottie_T — Fri, 26 Mar 2021 15:52:50 GMT

I have attached the proc contents of this code.

Re: Case Statement Error in Flagging

Sajid01 — Fri, 26 Mar 2021 18:41:13 GMT

Please have a look here

data temp;
length id Loc_No Prod_Ctr_Num $4.;
input id Loc_no Prod_Ctr_Num;
Datalines ;
1 2102 2102
2 3340 7370
3 5590 4370
;
run;
proc sql;
select *,
case when (m.loc_no = m.Prod_Ctr_Num) or m.Prod_Ctr_Num = '7370'
then "N"
else "Y"
end as Shares_Flag from temp m;
quit;

The output looks as you have wanted.

Thus when Loc_No and Prod_Ctr are equal you get N, when Prod_Ctr is 7370 then also there is N.

Are you looking for something different?

Re: Case Statement Error in Flagging

Kurt_Bremser — Fri, 26 Mar 2021 18:55:11 GMT

According to your PROC CONTENTS output, there is no variable LOC_NO in your dataset.

Re: Case Statement Error in Flagging

Scottie_T — Fri, 26 Mar 2021 21:46:32 GMT

I was renaming over a column with a separate statement so it was not recognizing the two characters as the same. I separated all the calculations out into separate columns and found where the statements were getting confused. Then the case statement worked great it was all about the naming and not writing over the existing data.

Thank you everyone for your help on this!!

Scottie