BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jacobfitz
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
Reeza
Super User

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.

jacobfitz
Fluorite | Level 6

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.

Reeza
Super User

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;

jacobfitz
Fluorite | Level 6

Thank you! This is exactly what I needed.

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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