BookmarkSubscribeRSS Feed
SASuserlot
Barite | Level 11

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

SASuserlot_2-1662147373883.png

 

 

Data How I want

SASuserlot_3-1662147413521.png

 

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;
4 REPLIES 4
ballardw
Super User

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.

 

SASuserlot
Barite | Level 11

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.

 

 

SASuserlot
Barite | Level 11

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

 

 

Quentin
Super User

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.

 

BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 869 views
  • 0 likes
  • 3 in conversation