Hi all,
I am using proc freq data step to find the txchari11 but along with it I also want RPC next to it. For e.g.,
txchari11 |
||
txchari11 | Frequency | RPC |
CRABlend | 16338 | ? |
Experian | 742 | ? |
Equifax | 4876 | ? |
Can you please suggest to get the RPC frequency along with number of txchari11? I want to find the number of RPC in each txchari11. Here is my sample data
Data excel_format_3;
input Accounts Collected txchari11 Trace_result RPC;
datalines;
45461 521 Experian LAS 11
65454 24 CRABlend NEW 54
24534 68 Equifax NEG 23
;
run;
If you want a report like the one you show, use PROC REPORT. And explain in more detail what goes in the RPC column ... it there are 16338 values for txchari11, wouldn't there also be 16338 in the RPC column?
However, your SAS code produces errors, I request that you fix it (and I request that you test your code before providing it to us — testing your code is a good thing, you will get faster and more likely correct answers)
Thank you for the reply.
RPC is a numeric column which has the number of time the person is contacted. txchari11 has different categories like Experian, Equifax, customer confirmed, CRAblend etc. So I am trying to find number of different txchari11 and along with it RPC. I can get the number of different txchari11 by using proc freq data step but trying to add RPC along with it. Example Experian has 150 frequency out of 1000 accounts. Along with 150 how many RPC frequency was there in Experian?
Also what is the error with the sample dataset as it runs fine in my SAS platform?
1 Data excel_format_3; 2 input Accounts Collected txchari11 Trace_result RPC; 3 datalines; NOTE: Invalid data for txchari11 in line 4 11-18. NOTE: Invalid data for Trace_result in line 4 20-22. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 4 45461 521 Experian LAS 11 Accounts=45461 Collected=521 txchari11=. Trace_result=. RPC=11 _ERROR_=1 _N_=1 NOTE: Invalid data for txchari11 in line 5 10-17. NOTE: Invalid data for Trace_result in line 5 19-21. 5 65454 24 CRABlend NEW 54 Accounts=65454 Collected=24 txchari11=. Trace_result=. RPC=54 _ERROR_=1 _N_=2 NOTE: Invalid data for txchari11 in line 6 10-16. NOTE: Invalid data for Trace_result in line 6 18-20. 6 24534 68 Equifax NEG 23 Accounts=24534 Collected=68 txchari11=. Trace_result=. RPC=23 _ERROR_=1 _N_=3 NOTE: The data set WORK.EXCEL_FORMAT_3 has 3 observations and 5 variables. NOTE: Compressing data set WORK.EXCEL_FORMAT_3 increased size by 100.00 percent.
Based on your explanation of RPC, do you want the SUM of RPC for each value of txchari11? You didn't say that, I'm guessing
Apologies for that. Yes, the sum of RPCs. I am trying to find the sum of each CRAs and RPC. So the table would look like below
txchari11 |
||
txchari11 | Frequency | RPC |
CRABlend | 16338 | 126 |
Experian | 742 | 249 |
Equifax | 4876 | 56 |
Then proc summary (alias proc means) will do what you request:
proc summary data=excel_format_3 nway;
class txchari11;
output out=want (rename=(_freq_=frequency) drop=_type_) sum(rpc)=rpc_sum;
run;
proc print;run;
@Sandeep77 wrote:
Apologies for that. Yes, the sum of RPCs. I am trying to find the sum of each CRAs and RPC. So the table would look like below
txchari11
txchari11 Frequency RPC CRABlend 16338 126 Experian 742 249 Equifax 4876 56
Proc Freq doesn't Sum anything. So you need to be a bit clearer about what you are doing. Possibly use a smaller SAS supplied data set such as SASHELP.Class and the Sex and Age variables as an example. There are only 19 observations in the set so you should be able to manually count or sum(?) one of them easily enough. That way we can avoid the whole data step that doesn't run issue.
Try using PROC SUMMARY instead of PROC FREQ so you can get count and sum in one step.
What number do you want in the FREQUENCY variable? The number of observations? Or the number of observations with a non-missing value of RPC?
Let's add an observation with a missing value of RPC so you can see the difference.
data have ;
length Accounts $5 Collected 8 txchari11 $20 Trace_result $3 RPC 8;
input Accounts--RPC;
datalines;
45461 521 Experian LAS 11
65454 24 CRABlend NEW 54
24534 68 Equifax NEG 23
xxxx 0 Equifax YYY .
;
proc summary data=have;
class txchari11;
var rpc;
output out=want n=Frequency sum=rpc_sum;
run;
proc print;
run;
Obs txchari11 _TYPE_ _FREQ_ Frequency rpc_sum
1 0 4 3 88
2 CRABlend 1 1 1 54
3 Equifax 1 2 1 23
4 Experian 1 1 1 11
If you don't want that overall summary line (_TYPE_=0) then add the NWAY option to the PROC SUMMARY statement.
"along with 150 how many RPC frequency was there in Experian".
It sounds like you want either:
Could you show enough of what you want to help clarify?
BTW, the data code you provide generates the following error message when I try to use it, as follows:
218 Data excel_format_3;
219 input Accounts Collected txchari11 Trace_result RPC;
220 datalines;
NOTE: Invalid data for txchari11 in line 221 11-18.
NOTE: Invalid data for Trace_result in line 221 20-22.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
221 45461 521 Experian LAS 11
Accounts=45461 Collected=521 txchari11=. Trace_result=. RPC=11 _ERROR_=1 _N_=1
NOTE: Invalid data for txchari11 in line 222 10-17.
NOTE: Invalid data for Trace_result in line 222 19-21.
222 65454 24 CRABlend NEW 54
Accounts=65454 Collected=24 txchari11=. Trace_result=. RPC=54 _ERROR_=1 _N_=2
NOTE: Invalid data for txchari11 in line 223 10-16.
NOTE: Invalid data for Trace_result in line 223 18-20.
223 24534 68 Equifax NEG 23
Accounts=24534 Collected=68 txchari11=. Trace_result=. RPC=23 _ERROR_=1 _N_=3
NOTE: The data set WORK.EXCEL_FORMAT_3 has 3 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
The input statement implies all the variables are numeric, but the data shows theat txchari11 and trace_result should be input as character variables.
proc freq data=excel_format_3;
table txchari11*rpc / list;
run;
Try adding it in with an asterisks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.