To count the number of IDs for which the trans_amt remains same in all observations of that ID.

[paulsud1]

Hi everyone, I am a newbie in SAS. 

I have a large data set with numerous variables two of which are "ID" and "Trans_amt"

What I want to find is to count the number of IDs for which the trans_amt remains same in all observations of that ID. 

ID       Trans_amt  

1          1000

1          1000

1          1000

1          1000

2            500

2            500

2            500

3            750

3            750

3            800

4           1200

4           1500

4           1200

Example for ID 1 the Trans_amt is 1000 for all 4 cases right? I want to create a new variable ID_1 where it stored the ID of those only. 

Thanks a lot in advance.

[PeterClemmensen]

@paulsud1 hi and welcome to the SAS Community 🙂

Can you post what you want your desired result to look like? Just like you posted usable example data. And kudos for that

[PeterClemmensen]

This gives you an indicator variable of 1 if the Trans_amt is constant for the entire group and 0 if it has multiple values

data have;
input ID Trans_amt;
datalines;
1 1000
1 1000
1 1000
1 1000
2 500
2 500
2 500
3 750
3 750
3 800
4 1200
4 1500
4 1200
;

proc sql;
    create table want as
    select *, (count (distinct Trans_amt))=1 as ind
    from have 
    group by ID;
quit;

[ed_sas_member · Accepted solution]

Hi @paulsud1 

I am not sure whether this code could satisfy your request

data have;
	input ID Trans_amt;
	cards;
1 1000
1 1000
1 1000
2 500
2 500
2 500
3 750
3 750
3 800
4 1200
4 1500
4 1200
;
run;

/* Retrieve id for which all Trans_amt are the same */
proc sort data=have;
	by ID Trans_amt;
run;

data ID_list;
	set have;
	by ID;
	if first.id or Trans_amt=lag(Trans_amt) then id_1=1;
	else id_1=0;
	if last.id then output;
	keep id id_1;
run;

/* Merge with original dataset */
proc sort data=ID_list;
	by ID ;
run;

data want;
	merge have ID_list;
	by ID;
run;