topic Re: Check if IDs have always the same value of a variable in SAS Programming

Check if IDs have always the same value of a variable

NewUsrStat — Mon, 26 Sep 2022 16:25:49 GMT

Hi guys,

is there a way to check, for each ID if the value of a variable is always the same like for example 54?

I would like to do an if statement only for those IDs where the value of variable x is only and always 54.

Thank you in advance

Re: Check if IDs have always the same value of a variable

Quentin — Mon, 26 Sep 2022 16:28:39 GMT

Your question is a little hard to understand. I suggest you edit your question to include a small amount of sample data (like 10 rows), and describe which records you want to select with your IF statement, and why.

Re: Check if IDs have always the same value of a variable

PaigeMiller — Mon, 26 Sep 2022 16:30:39 GMT

One way:

proc summary data=have nway;
    class id;
    var x;
    output out=_stats_ min=x_min max=x_max;
run;

In this example, if the x_min and x_max are the same value, that would meet your desired condition of all the same value of x. So that would involve doing a comparison. You can take this idea one step further and eliminate the need to do a comparison with this:

output out=_stats_ range=x_range;

and if the x_range is zero, then all the x-values are the same.

Re: Check if IDs have always the same value of a variable

NewUsrStat — Mon, 26 Sep 2022 17:16:14 GMT

It seems I cannot edit. Briefly: suppose to have a file that looks like this:
ID Value
a 54
a 54
a 54
a 54
b 54
b 32
b 54

and you want to for example add a label in a new column that contains 1 only for ID =a that has only and always 54 in column value

Re: Check if IDs have always the same value of a variable

Quentin — Mon, 26 Sep 2022 17:27:45 GMT

Is the value 54 part of the criteria or is the criteria just constant value within an ID? So if you had:

ID Value
a 54
a 54
a 54
a 54
b 54
b 32
b 54
c 32
c 32

Would you want to flag id=c?

Can you show what code you have tried?

Are you comfortable with SQL? Are you familiar with data step DOW-loop pattern?

Re: Check if IDs have always the same value of a variable

PaigeMiller — Mon, 26 Sep 2022 17:39:58 GMT

Adding to my earlier reply, this will give you a column of ones (when the value of x is constant) and zeros (when the value of x is not constant).

data want;
     merge have _stats_;
     by id;
     new_column = (x_range=0);
run;

Re: Check if IDs have always the same value of a variable

Quentin — Mon, 26 Sep 2022 19:41:31 GMT

Here's a SQL approach. This makes use of the fact that PROC SQL will 'remerge' automatically, which many SQL folks don't like. Not sure how you want to handle missing values. This will not treat a missing value as different.

data have ;
  input id $1 Value ;
  cards ;
a 54
a 54
a 54
a 54
b 54
b 32
b 54
c 32
c 32
d 60
d .
e .
e .
;

proc sql ;
  select id,Value,range(value)=0 as Constant 
  from have
  group by id
  ;
quit ;

Returns:

id     Value  Constant
----------------------
a         54         1
a         54         1
a         54         1
a         54         1
b         54         0
b         32         0
b         54         0
c         32         1
c         32         1
d          .         1
d         60         1
e          .         0
e          .         0