DATA Step, Macro, Functions and more

shared subgroup values

Reply
Frequent Contributor
Posts: 79

shared subgroup values

Hi Experts,

 

I have a data like given below:

 

ID Attempts
1000000002 A2
1000000002 A2
1000000002 A1
1000000002 NA
1000000003 NA
1000000007 NA
1000000008 A3
1000000017 NA
1000000018 A1
1000000018 A2
1000000018 A3
1000000018 A2
1000000018 NA
1000000019 A1
1000000019 A3
1000000019 NA
1000000020 A1
1000000020 A3
1000000022 A2

 

want the output as 

ID Attempts Attemptbyid shared
1000000002 A2 1 0.33
1000000002 A2 0 0.33
1000000002 A1 0 0.33
1000000002 NA 0 0
1000000003 NA 0 0
1000000007 NA 0 0
1000000008 A3 1 1
1000000017 NA 0 0
1000000018 A1 1 0.25
1000000018 A2 0 0.25
1000000018 A3 0 0.25
1000000018 A2 0 0.25
1000000018 NA 0 0
1000000019 A1 1 0.5
1000000019 A3 0 0.5
1000000019 NA 0 0
1000000020 A1 1 0.5
1000000020 A3 0 0.5
1000000022 A2 1 0

 

need to created a varialble Shared which should have the total count of distince IDs where IDs with attemps NA has to be excluded.

created Attemptbyid variable as count for IDs exculuding NAs but have to distribute distinct count (Attemptbyid) 1 to all subgroup observations.

 

thanks

Super User
Super User
Posts: 9,599

Re: shared subgroup values

Posted in reply to Rahul_SAS

As I am not going to type that test data in, this is untested.

proc sql;
  create table want as
  select a.*,
         (select count(attempts) from have where id=a.id and attempts=a.attempts) / 
(select count(attempts) from have where id=a.id and attempts ne "NA") as shared from have a; quit;
Frequent Contributor
Posts: 79

Re: shared subgroup values

HI RW9,

 

its not giving the desired result.

 

 

Example:

1000000018 NA
1000000018 A2
1000000018 A2
1000000018 A1

 

for this ID, the distinct count is 1 and excluding NA total Attempts is 3 so i need 1/3=.33 to be populated to all rows.

1000000018 NA 0 0
1000000018 A2 1 0.33
1000000018 A2 0 0.33
1000000018 A1 0 0.33

similarly if there are 4 Attempts then 1/4=.25 to be populated in Attempts

Super User
Super User
Posts: 9,599

Re: shared subgroup values

Posted in reply to Rahul_SAS

"As I am not going to type that test data in, this is untested." - and am still not typing test data in.

Why is distinct count 1?  There is at least 3 distinct values in the example provided- A1, A2, NA.

Frequent Contributor
Posts: 79

Re: shared subgroup values

I have to get this as 1 to match the numbers.

if it gets populated for all rows then the total could will be so high and won't give correct result while aggregation.

Super User
Posts: 23,663

Re: shared subgroup values

Posted in reply to Rahul_SAS
data temp;
set have;

group = substr(attempts, 1, 1);
run;

proc sql;
create table want as 
select *, 1/count(*) as mean_value
from temp
group by id, group;
quit;

@Rahul_SAS wrote:

Hi Experts,

 

I have a data like given below:

 

ID Attempts
1000000002 A2
1000000002 A2
1000000002 A1
1000000002 NA
1000000003 NA
1000000007 NA
1000000008 A3
1000000017 NA
1000000018 A1
1000000018 A2
1000000018 A3
1000000018 A2
1000000018 NA
1000000019 A1
1000000019 A3
1000000019 NA
1000000020 A1
1000000020 A3
1000000022 A2

 

want the output as 

ID Attempts Attemptbyid shared
1000000002 A2 1 0.33
1000000002 A2 0 0.33
1000000002 A1 0 0.33
1000000002 NA 0 0
1000000003 NA 0 0
1000000007 NA 0 0
1000000008 A3 1 1
1000000017 NA 0 0
1000000018 A1 1 0.25
1000000018 A2 0 0.25
1000000018 A3 0 0.25
1000000018 A2 0 0.25
1000000018 NA 0 0
1000000019 A1 1 0.5
1000000019 A3 0 0.5
1000000019 NA 0 0
1000000020 A1 1 0.5
1000000020 A3 0 0.5
1000000022 A2 1 0

 

need to created a varialble Shared which should have the total count of distince IDs where IDs with attemps NA has to be excluded.

created Attemptbyid variable as count for IDs exculuding NAs but have to distribute distinct count (Attemptbyid) 1 to all subgroup observations.

 

thanks


 

Ask a Question
Discussion stats
  • 5 replies
  • 83 views
  • 0 likes
  • 3 in conversation