BookmarkSubscribeRSS Feed
jbscholten
Obsidian | Level 7

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]

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jbscholten
Obsidian | Level 7

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.

jbscholten
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jbscholten
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ChrisNZ
Tourmaline | Level 20

Just do

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 8 replies
  • 1948 views
  • 6 likes
  • 3 in conversation