topic Re: Checking if variables are equal? in SAS Programming

Checking if variables are equal?

jbscholten — Wed, 08 Jul 2015 12:53:59 GMT

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]

Re: Checking if variables are equal?

RW9 — Wed, 08 Jul 2015 13:28:30 GMT

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.

Re: Checking if variables are equal?

jbscholten — Wed, 08 Jul 2015 13:38:37 GMT

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.

Re: Checking if variables are equal?

jbscholten — Wed, 08 Jul 2015 13:41:20 GMT

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.

Re: Checking if variables are equal?

RW9 — Wed, 08 Jul 2015 17:07:51 GMT

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;

Re: Checking if variables are equal?

RW9 — Thu, 09 Jul 2015 09:04:03 GMT

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.

Re: Checking if variables are equal?

ChrisNZ — Thu, 09 Jul 2015 11:04:18 GMT

Just do

if min(of ErrorTypeFV:) = max(of ErrorTypeFV:);

Re: Checking if variables are equal?

jbscholten — Thu, 09 Jul 2015 20:08:28 GMT

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

Re: Checking if variables are equal?

RW9 — Fri, 10 Jul 2015 07:57:51 GMT

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.