topic How to Create the Variable and Create an alert in the Log based on the Data condition? in SAS Programming

How to Create the Variable and Create an alert in the Log based on the Data condition?

SASuserlot — Fri, 02 Sep 2022 19:38:22 GMT

Sorry If my title is little confused. Not sure how to paraphrase it.

This is my situation in my data. I am looking to find a way to create a variable and an alert based on the conditions I want. I can use the 'IF' conditions . But it looks not efficient. Is there any way I can achieve this.

I have a dataset with two flags fl1, fl2 and 3 numeric scores x1, y1 and z1. But my data ( Id=104) have the errors based on following conditions.

Condition 1: if (fl1='Y' and fl2= 'Y') or (fl1='Y' and fl2= 'N') or (fl1='N' and fl2= 'Y') then at least one score should be available.

Condition 2: If (fl1='N' and fl2= 'N') then x1, y1 and z1 should not have any errors.

Based on the above condition I need to create a Variable 'Result' based on it, Also looking for an Alert to show in the 'LOG' which ID have the Result='wrong';

Data have

Data How I want

Note: for Example purpose I give 3 variables but my data have 15 numeric scores.

Thank you for your time.

 data have;
 input id fl1$ fl2$ x1 y1 z1 ;
  cards;
 101 Y Y 1 1 6
 102 Y N 1 4 1
 103 N Y 1 1 3
 104 N N 1 1 1 
 105 N N . . .
 106 Y N 1 . 2
 107 N Y 3 . 1
 108 Y Y . . .
 ;
 run;

Re: How to Create the Variable and Create an alert in the Log based on the Data condition?

ballardw — Fri, 02 Sep 2022 19:55:28 GMT

With: Condition 1: if (fl1='Y' and fl2= 'Y') or (fl1='Y' and fl2= 'N') or (fl1='N' and fl2= 'Y') then at least one score should be available. How is this to be used. It seems to be a statement, not an action or instruction to perform an action.

From: Condition 2: If (fl1='N' and fl2= 'N') then x1, y1 and z1 should not have any errors.

How do we know if x1, y1 or z1 have an error?

When processing something with a data step you can use PUT statements to write to the log.

Depending on your environment if your message starts with "WARNING" or "ERROR" the text in the log may have a special appearance.

Run this code and look in the Log for appearance:

data _null_;
   set sashelp.class;
   if age=11 then put "WARNING: Age is 11 for " name=;
run;

Note that if you use numeric 1/0 codes for "flags" like that instead of 'Y' 'N' or similar character values your SAS code can get quite a bit easier to write. SAS will treat 0 or missing numeric values as "False" or "No" or what have you and other numbers as 'True' or 'Yes'.

With multiple "flags" you can often do comparisons with numeric functions.

If you want to know if any of the flags is true (1) then if max(flag1,flag2,flag3, ...) = 1.

You can tell if all the flags are the same with the Range function, which would return 0 when all are the same.

You can count how many "yes" values with sum: Countyes = sum(flag1, flag2, flag3, ...);

Percent of "yes" with: Percentyes = mean(flag1,flag2,flag3, ...); The mean of a 1/0 coded variable is a decimal percentage: .33333 = 33.33 percent.

Re: How to Create the Variable and Create an alert in the Log based on the Data condition?

SASuserlot — Fri, 02 Sep 2022 20:19:06 GMT

Sorry for confusion.

1. Yes those are instructions. Not conditions. Sorry for confusion.

2. Based on fl1 fl2 flags. for ID=104 should have the missing values because fl1 and fl2 have 'N'. This is like gatekeeping question to enter the scores. x1, y1, z1 only have scores when one of the flag have the 'Y'

3. It worked thanks for the Log alert.

4. We/ You are free to change type of data. I given flags (fl1, fl2) as characters , so that it will be easy to explain the dataset.

Re: How to Create the Variable and Create an alert in the Log based on the Data condition?

SASuserlot — Fri, 02 Sep 2022 20:19:26 GMT

Sorry for confusion.

1. Yes those are instructions. Not conditions. Sorry for confusion.

3. It worked thanks for the Log alert.

4. We/ You are free to change type of data. I given flags (fl1, fl2) as

Re: How to Create the Variable and Create an alert in the Log based on the Data condition?

Quentin — Fri, 02 Sep 2022 22:05:39 GMT

Hi,

This sort of check is known as an assertion, and is common in other languages. Many languages have an ASSERT() statement. The idea is that you write an expression which you expect to to be TRUE, and if it turns out to be FALSE, you want an error in the log, and you want to create a new variable that will show you which records failed the assertion.

While SAS doesn't have an ASSERT statement, you can use a macro to make your own. I wrote a paper where I presented an %ASSERT macro: https://www.lexjansen.com/nesug/nesug12/cc/cc31.pdf

The most basic assert macro is just:

%macro assert(assertion);
  if NOT (&assertion) then putlog "ERROR: Assertion (%superq(assertion)) is FALSE.";
%mend assert;

With that, you can do simple assertions like:

data want ;
  set have ;
  %assert(fl1 IN ('Y' 'N')) /*assert fl1 is always within expected range*/
  %assert(fl2 IN ('Y' 'N'))
run ;

When I read your logic, I see it as a single assertion. I think your logic is: X1, Y1, and Z1 should all be missing, if and only if Fl1='N' and FL2='N' . So it's like a skip-pattern. Because of the way SAS does boolean logic, you can write that assertion as a single expression:

data want ;
  set have ;
  %assert(  (fl1='N' and fl2='N') = (n(x1,y1,z1)=0)  )
run ;

That assertion is checking that if the left side of the expression is true, then the right side must be true, and also checks if the left side is false, then the right side must be false.

In the paper I have a longer version of the macro, which adds a parameter to write a custom error message, and a parameter to create a new RESULT variable like you want. With that macro, you can code:

data have;
  input id fl1$ fl2$ x1 y1 z1 ;
  cards;
 101 Y Y 1 1 6
 102 Y N 1 4 1
 103 N Y 1 1 3
 104 N N 1 1 1 
 105 N N . . .
 106 Y N 1 . 2
 107 N Y 3 . 1
 108 Y Y . . .
 ;
 run;

data want ;
  set have ;

  %assert(fl1 IN ('Y' 'N'))
  %assert(fl2 IN ('Y' 'N'))

  %assert( (fl1='N' and fl2='N') = (n(x1,y1,z1)=0)
         ,msg="Mismatch between flags and scores: " (id fl1 fl2 x1 y1 z1)(=)
         ,errorflag=Result
         )
run ;
   
proc print data=want ;
run ;

And the log will flag two records as failing the assertion:

15   data want ;
16     set have ;
17
18     %assert(fl1 IN ('Y' 'N'))
19     %assert(fl2 IN ('Y' 'N'))
20
21     %assert( (fl1='N' and fl2='N') = (n(x1,y1,z1)=0)
22            ,msg="Mismatch between flags and scores: " (id fl1 fl2 x1 y1 z1)(=)
23            ,errflag=Result
24            )
25   run ;

ERROR: USER ((fl1='N' and fl2='N') = (n(x1,y1,z1)=0)) is FALSE. Mismatch between flags and scores:
id=104 fl1=N fl2=N x1=1 y1=1 z1=1
ERROR: USER ((fl1='N' and fl2='N') = (n(x1,y1,z1)=0)) is FALSE. Mismatch between flags and scores:
id=108 fl1=Y fl2=Y x1=. y1=. z1=.
NOTE: There were 8 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 8 observations and 7 variables.

And the RESULT variable will show will flag them as well:

Obs     id    fl1    fl2    x1    y1    z1    Result

 1     101     Y      Y      1     1     6       0
 2     102     Y      N      1     4     1       0
 3     103     N      Y      1     1     3       0
 4     104     N      N      1     1     1       1
 5     105     N      N      .     .     .       0
 6     106     Y      N      1     .     2       0
 7     107     N      Y      3     .     1       0
 8     108     Y      Y      .     .     .       1

HTH,

--Q.