DATA Step, Macro, Functions and more

PROC MEANS DISTINCT VALUES

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

PROC MEANS DISTINCT VALUES

Hello,

 

I have the following data set and need to summarize in the manner of the following code. However, instead of outputting to total for the variable PS, I need the distinct total only.

 

proc means data=WORK.TEST noprint;

by R TC RN SS SH;

var PS;

output out= TEST1 sum (E)=E_F n(PS)=NB_PS;

run;

 

RNETCRSSSHPS
17075003A02SS 2028106601
16985003A02SS 2028106601
111195003A02SS 2028106601
120995003A02  2028612941
113705003A02  2024667991
18655003A02  2028221751
130265003A02  2028221751
110085003A02  2025744721
16046003A09 SH2027282411
112856003A09 SH2027282411
121076003A09  2025877621
18546003A09  2025250411
19556003A09  2024224891
15026003A09  2022288221

 

So instead of getting the output:

 

RTCRNSSSHE_FNB_PS
A0250031SS  83683
A0250031  25245
A0960031 SH18892
A0960031  44184

 

 

I need the output:

 

RTCRNSSSHE_FNB_PS
A0250031SS  83681
A0250031  25244
A0960031 SH18891
A0960031  44184

 

 

Thank you for any help!


Accepted Solutions
Solution
‎04-18-2017 04:50 PM
Super User
Posts: 17,868

Re: PROC MEANS DISTINCT VALUES

Here's the structure for a PROC SQL solution. 

 

Proc sql;

create table want as

select R, TC, RN, SS, SH, /*grouping variables*/

 

/*aggregate functions*/

sum(e) as e_f,

count(distinct PS) as N_PS

 

from test

group by R, TC, RN, SS, SH

quit;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: PROC MEANS DISTINCT VALUES

Explain your logic please?

Where does RN come from?

Do you need the SUM for all records or only the distinct records and how do you identify the distinct records if this is the case?

 

Proc means cannot do a distinct count, you can try SQL or a double PROC FREQ instead.

New Contributor
Posts: 3

Re: PROC MEANS DISTINCT VALUES

[ Edited ]

RN is a number assigned to each PS. It can be 1,51,55 etc. In this specific example they are all 1.

 

I need E_F to be the sum of all E but NB_PS needs to be the count of distinct PS. I am not sure how to get the output I need using SQL or PROC FREQ.

Solution
‎04-18-2017 04:50 PM
Super User
Posts: 17,868

Re: PROC MEANS DISTINCT VALUES

Here's the structure for a PROC SQL solution. 

 

Proc sql;

create table want as

select R, TC, RN, SS, SH, /*grouping variables*/

 

/*aggregate functions*/

sum(e) as e_f,

count(distinct PS) as N_PS

 

from test

group by R, TC, RN, SS, SH

quit;

New Contributor
Posts: 3

Re: PROC MEANS DISTINCT VALUES

Thank you! This is exactly what I needed.

Trusted Advisor
Posts: 1,621

Re: PROC MEANS DISTINCT VALUES

This is a job for PROC FREQ, with a BY statement.

 

The first run of PROC FREQ will tell you, for example, that 2028106601 appears 3 times in it's BY group. The second run of PROC FREQ will tell you that there is only one value of NB_PS in that BY group.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 175 views
  • 2 likes
  • 3 in conversation