Can proc freq data can get information of more than one column?

[Sandeep77]

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;

[PaigeMiller]

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)

[Sandeep77]

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?

[PaigeMiller]

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

[Sandeep77]

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

[mkeintz]

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;

[ballardw]

---

@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.

[Tom]

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.

[mkeintz]

---

"along with 150 how many RPC frequency was there in Experian".

It sounds like you want either:

1. a crosstabulation showing the frequency of various RPC values for each value of txchari11
2. or possibly the SUM of RPC for each category of txchari11.

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.

[Reeza]

proc freq data=excel_format_3;
table txchari11*rpc / list;
run;

Try adding it in with an asterisks.