DATA Step, Macro, Functions and more

Checking if variables are equal?

Reply
Occasional Contributor
Posts: 13

Checking if variables are equal?

Hi all, I'm trying to check if certain variables from a dataset are equal or blank.

There are 28 variables that I have, ErrorTypeFV_1, ErrorTypeFV_2, ..... ErrorTypeFV_28, one for each year and each can have 4 values, 1,2,3, or 4.

Basically what I want to check is that for all rows, if all ErrorTypeFV_1 through ErrorTypeFV_28 are EQUAL,  then to set a new variable, "TYPE" to the value of these variables.

The issue is that some of these years 1-28 can be blank, and this is okay. I just want to check that all variables that are not blank are equal.

Additionally, if there are two different values in any row I would like "TYPE" to be set to mixed.

Any help would be greatly appreciated.

I've an example I constructed in excel, the years after ErrorType are not present.

http://i59.tinypic.com/sm9bbt.png[/IMG]

Super User
Super User
Posts: 7,970

Re: Checking if variables are equal?

Posted in reply to jbscholten

Well you could use arrays:

data want;

     set have;

     array errortypefv_{28};

     type=errortypefv_{1};

     do i=2 to 28;

          if errortypefv_{i} ne . and type ne errortypefv_{i} then type=.;

     end;

run;

You can put any checking in the do loop, I just stated set type to first value, then if not missing, and not equal to the first value then set type to missing.

Also, please post test data in the form of a datastep, that picture is unhelpful and looks like a dodgy website.

Occasional Contributor
Posts: 13

Re: Checking if variables are equal?

I see, thank you RW9. I've never worked with arrays.

After checking, this works, but ONLY if errortypefv_1 is not blank.  If FV_1 is blank then type is returning blank as well.

Occasional Contributor
Posts: 13

Re: Checking if variables are equal?

Thank you, RW9! Never worked with arrays before.

This works, however only for rows in which errortypeFV_1 is not blank.

I need this to pick up the first populated errorType field. and then check if the rest are the same.

Super User
Super User
Posts: 7,970

Re: Checking if variables are equal?

Posted in reply to jbscholten

Slight modification then (note I can't check this at the moment - unsure about the whichn syntax from memory):

data want;

     set have;

     array errortypefv_{28};

     type=errortypefv_{whichn(> .,of errortypefv_{*})};

     if whichn(!= type,of errortypefv_{*}) > 0 then flag=1;

run;

If the above doesn't work (and will check it tomorrow), then just use loops:

data want;

     set have;

     array errortypefv_{28};

     i=1;

     do until (type ne . or i > 28);

         if errortypefv_{i} > . then type=errortypefv_{i};

     end;

     do i=1 to 28;

          if errortypefv_{i} ne . and errortypefv_{i} ne type then flag=1;

     end;

run;

Super User
Super User
Posts: 7,970

Re: Checking if variables are equal?

Hi,

Just to follow up, that's not the syntax of whichn.  However I did think up this little bit of code:

data have;

  infile datalines dlm=",";

  input errortypefv_1-errortypefv_5;

datalines;

.,.,5,.,5

1,2,3,4,5

.,2,2,2,.

2,.,.,.,2

;

run;

data want (drop=tmp);

  set have;

  array errortypefv_{5};

  tmp=compress(cats(of errortypefv_{*}),".");

  if lengthn(compress(tmp,substr(tmp,1,1))) > 0 then flag=1;

run;

Basically it create a string of your numbers, removes the missings, then removes all occurrences of the first character, anything left indicates there is a difference.

Occasional Contributor
Posts: 13

Re: Checking if variables are equal?

Thanks for the help.

I ended up just creating new variables ex type 1 error type 2 error type 3 error and so on...

if whichever type of ever is present then set a 1 in the new variable, if sum of new variables >1 then errorType = mixed

Smiley Happy

Super User
Super User
Posts: 7,970

Re: Checking if variables are equal?

Posted in reply to jbscholten

Well, you would still be better off normalising the data, then you can just use aggregates:

errortypefv_no     errortypefv_result

1                              .

2                              1

3                              2

4                              .

5                              .

...

Depending on what else you do with the data can also help further on as well.

PROC Star
Posts: 1,760

Re: Checking if variables are equal?

Posted in reply to jbscholten

Just do

if min(of ErrorTypeFVSmiley Happy = max(of ErrorTypeFVSmiley Happy;

Ask a Question
Discussion stats
  • 8 replies
  • 399 views
  • 6 likes
  • 3 in conversation