topic Re: To count the number of IDs for which the trans_amt remains same in all observations of that ID. in SAS Programming

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

paulsud1 — Wed, 16 Oct 2019 14:17:12 GMT

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

2 500

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.

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

PeterClemmensen — Wed, 16 Oct 2019 14:21:48 GMT

@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

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

PeterClemmensen — Wed, 16 Oct 2019 14:25:33 GMT

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;

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

ed_sas_member — Wed, 16 Oct 2019 14:53:03 GMT

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;