392549_en-US

liliconcom

Conditional value labels for missing values

I wonder if there's a way to set up conditional value labels based on values from two columns. If I refer to the snapshot table below, I have "column 1" which could either be filled or empty and I also have "column 2" which either be filled or empty. What I'm trying to do is for "column 2" to be formatted like "Column 3". 

Which is: (i) values formatted to a date format if there's a value AND the "column 1" value on the same row is not empty, (ii) To display "Not Credible" if the "Column 2" value is missing but "Column 1" value on the same row is not empty, and (iii) to remain as "." or empty if both "column 1" & "column 2" values on the same row are missing. 

 

 

Appreciate the help. 

 

Data TableScriptingRe: Conditional value labels for missing values

Hi, 

The task you presented to us is a bit tricky because you wont both text "Not Credible" and Dates in the same column. Thus, assuming that your Column 2 dates are entered as numbers (no leading "0" for month < 10) and that the order of the day - month - year is always the same, here is a formula that should do the trick:

DATEIN = Char( :Column 2 );
DATEOUT = "";
If( !Is Missing( :Column 1 ),
 If( !Is Missing( :Column 2 ) & Is Number( :Column 2 ),
  If(
   Length( DATEIN ) == 8,
    DATEOUT = Format( Informat( DATEIN, "mmddyyyy" ), "ddMonyyyy" ),
   Length( Char( DATEIN ) ) == 7,
    DATEIN = "0" || DATEIN;
    DATEOUT = Format( Informat( DATEIN, "mmddyyy" ), "ddMonyyyy" );
  )
 ,
  DATEOUT = "Not Credible"
 )
,
 DATEOUT = "."
);
DATEOUT;

I intentionally used local variable to make the formula more readable but it is not really required.

Let us know if that works for you.

Best,

TS

Re: Conditional value labels for missing values

Thanks @Thierry_S. If I understand correctly, this would make the "column 2" column type to be "character", right ? 

What if I want to keep it as Numeric for further analysis ? The whole idea of differentiating blank and "not credible" is just for filtering purposes. I know I can make a new column for that but I wonder if there are other ways.