topic Re: counting distinct obervations in one colum and summing only the assotiated elements in another c in SAS Programming

counting distinct obervations in one colum and summing only the assotiated elements in another col

ejarquejm — Mon, 20 Jan 2020 12:38:24 GMT

counting distinct obervations in one col. and summing only the assotiated elements in another col
Here is my data.

First three columns are the relevant part. they have individuali.d., firm i.d., and age of each specific individual. due to other variables these first rows are repeated.

Data:

Indid firmid age , other variables

1 1 29

2 1 34

3 2 51

4 2 25

5 3 62

I am using sql and i would like to get

Firmid nworkers sumofages

1 2 63

2 2 76

3 1 62

Where of course 29+34 = 63 and 51 + 25 = 76.

Right now i can use (distinct indid) to count the number fo workers but i have not found the syntax to avoid duplicating the ages.

Thank you.

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

ed_sas_member — Mon, 20 Jan 2020 12:48:01 GMT

Hi @ejarquejm

You can try this:

proc sql;
	select firmid,
		   count(distinct Indid) as nworkers,
		   sum(distinct age) as sumofages
	from have
	group by firmid;
quit;

Best,

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

novinosrin — Mon, 20 Jan 2020 13:11:59 GMT

Take the straight forward SQL solution, there is some fun in datastep approach to this problem


data have;
input Indid    firmid   age;
cards;
1            1          29
1            1          29
2            1          34
2            1          34
3            2          51
3            2          51
3            2          51
4            2          25
5            3          62
;


data want;
 if 0 then set have;
 do nworkers=1 by 1 until(last.firmid);
  do until(last.indid);
   set have;
   by firmid indid;
   sumofages=sum(first.indid*age,sumofages);
  end;
 end;
 keep  Firmid   nworkers    sumofages;
run;

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

ejarquejm — Mon, 20 Jan 2020 13:26:50 GMT

Hi @ed_sas_member

That may not work because it is not age that has to be distinct.

It is the ages of the distinct individuals I need.

I should have written an example where 2 individuals have the same age. Apologies for that.

Consider then

Data:

Indid firmid age , other variables

1 1 29

2 1 29

3 2 51

4 2 25

5 3 62

I am using sql and i would like to get

Firmid nworkers sumofages

1 2 58

2 2 76

3 1 62

Thank you.

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

ejarquejm — Mon, 20 Jan 2020 13:28:43 GMT

Hi @novinosrin

this is interesting. let me try and get back to you.

thank you.

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

Ksharp — Mon, 20 Jan 2020 13:36:23 GMT

Pick up the obs you want and calculated it.

data temp;

set have;

by firmid Indid;

if first.Indid;

run;

proc summary data=temp out=want;

by firmid;

var age;

output out=want n=nworkers sum=sumofages;

run;

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

ejarquejm — Mon, 20 Jan 2020 13:40:02 GMT

Hi @novinosrin

This works

I can then merge with my outcome of sql to get what i want.

Thank you.

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

ejarquejm — Mon, 20 Jan 2020 13:41:54 GMT

Thank you @Ksharp

I will try this too.

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

novinosrin — Mon, 20 Jan 2020 13:46:24 GMT

Yes I personally love the nested construct and boolean expression. I learned this technique from Guru @hashman in one of his threads by further requesting him to detail everything I need to know. He was gracious. Oh well, we all learn and share. Ah, at least the sane ones. 🙂

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

novinosrin — Mon, 20 Jan 2020 14:23:03 GMT

@ejarquejm And if SQL is your comfort zone, here is a SQL solution

data have;
input Indid    firmid   age;
cards;
1            1          29
1            1          29
2            1          29
2            1          29
3            2          51
3            2          51
3            2          51
4            2          25
5            3          62
;

proc sql;
create table want as
select Firmid , count(distinct Indid) as nworkers, sum(age) as sumofages
from (select distinct firmid,indid,age from have) 
group by Firmid;
quit;

Re: counting distinct obervations in one colum and summing only the assotiated elements in another c

srujana_hm — Tue, 21 Jan 2020 06:57:33 GMT

data new ;
input inst firmid age;
cards;
1 1 29
1 1 29
2 1 29
2 1 29
3 2 16
3 2 32
4 2 21
4 2 21
;
run;

proc sql;
create table new1 as
select distinct firmid ,inst as nwork, age as total_age from new
group by firmid;
run;

proc sql;
create table new2 as
select distinct firmid ,count(inst) as cnt, sum(age)as tot from new
group by firmid;
run;

output is

firmid cnt tot

1 2 58

2 3 69

hope this will help.