Can I use proc freq data to generate multiple information together?

[Sandeep77]

Hi all,

I have written proc freq data step to get the number of accounts from different bureau source. But I also want to add the collections to those bureau source. Can I add collections also with the bureau source in proc freq data step to get both the information together? This will help me to see the collections value and number of accounts from different sources. I have used the below code to get the number of accounts from different sources: (tx_char_i11 is the header which shows all the sources) Thanks

proc freq data=excel_format_3;
table tx_char_i11 / nocum;
run;

[HarrySnart]

Hi @Sandeep77 

It would help if you gave an example of what you have and what you want to achieve with some dummy data. 

If I've understood your question correctly you want to view the data together you can add the variables to the table statement.

Two examples below:

*View two separate freq tables;
proc freq data=sashelp.class;
table age height / nocum;
run;

*View crosstab freq table;
proc freq data=sashelp.class;
table age * height / nocum;
run;

Hope this helps

Thanks

Harry

[Sandeep77]

Thank you Harry, I want the crosstab freq table and I tried doing that way but it says 'Large results were detected' and does not load. Can you suggest a way to resolve this issue?

[PaigeMiller]

---

@Sandeep77 wrote:

Thank you Harry, I want the crosstab freq table and I tried doing that way but it says 'Large results were detected' and does not load. Can you suggest a way to resolve this issue?

---

Show us the actual LOG. I'm guessing that "Large results were detected" is not the actual wording.

How many different levels of these variables (accounts and collections) are there?

[Sandeep77]

Hi,

'Large results were detected' is the exact wording that pops up. There are 2.6M records with 54 variables. I have pasted the log as well. 

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Summary';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='S:\Trace\Trace Integrity Analyst Data\BAU_Processes\Reports\ROI Final\Manual ROI V6.egp';
6          %LET _CLIENTPROJECTPATHHOST='LWLT5CG9322XFL';
7          %LET _CLIENTPROJECTNAME='Manual ROI V6.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         ODS LISTING GPATH=&sasworklocation;
18         FILENAME EGHTML TEMP;
19         ODS HTML5(ID=EGHTML) FILE=EGHTML
20             OPTIONS(BITMAP_MODE='INLINE')
21             %HTML5AccessibleGraphSupported
MPRINT(HTML5ACCESSIBLEGRAPHSUPPORTED):   ACCESSIBLE_GRAPH
22             ENCODING='utf-8'
23             STYLE=HTMLBlue
24             NOGTITLE
25             NOGFOOTNOTE
26             GPATH=&sasworklocation
27         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         proc freq data=excel_format_3;
30         table tx_char_i11 * Total_Payment_Amount/ nocum ;
31         run;

NOTE: There were 2689676 observations read from the data set WORK.EXCEL_FORMAT_3.
NOTE: The PROCEDURE FREQ printed pages 1-2049.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           53.42 seconds
      user cpu time       50.17 seconds
      system cpu time     0.84 seconds
      memory              60803.56k
      OS Memory           94460.00k
      Timestamp           01/27/2023 12:39:15 PM
      Step Count                        84  Switch Count  4
      

32         
33         
34         %LET _CLIENTTASKLABEL=;
35         %LET _CLIENTPROCESSFLOWNAME=;
36         %LET _CLIENTPROJECTPATH=;
37         %LET _CLIENTPROJECTPATHHOST=;
38         %LET _CLIENTPROJECTNAME=;
39         %LET _SASPROGRAMFILE=;
40         %LET _SASPROGRAMFILEHOST=;
41         
42         ;*';*";*/;quit;run;
43         ODS _ALL_ CLOSE;
2                                                          The SAS System                             09:34 Friday, January 27, 2023

44         
45         
46         QUIT; RUN;

[PaigeMiller]

'Large results were detected' is the exact wording that pops up. There are 2.6M records with 54 variables.

Thanks. You didn't answer my other question about how many levels the two variables tx_char_i11 and total_payment_amount have. But the idea of doing a PROC FREQ on total_payment_amount, if I understand what this variable means, is meaningless. FREQ is the wrong procedure. So, let's take a step back and please EXPLAIN the analysis you would like to do here, I'm not asking for SAS code, I want explanation.

[Sandeep77]

I have used proc freq step to get the number of accounts for different bureau source (tx_char_i11) from a previous table. There are 7 different variables in bureau source. Now I want to add another column with these 7 variables to see the collection amount for all these 7 variables. Here is a sample data set:

tx_char_i11
tx_char_i11	Frequency	Percent	Collection
Frequency Missing = 2231175
CRA Blend	83999	18.32	Need
Customer Confirmed	4747	1.04	to
Equifax	24915	5.43	add
Exp	319202	69.62	this
Registry	193	0.04	information
Call Report	25124	5.48
Retriever	321	0.07

[PaigeMiller]

Not making sense. Previously you had total_payment_amount and that doesn't appear in your most recent message.

Let me try this another way. Total_payment_amount is a value such as 29.03. Do you really want a table from PROC FREQ that shows how many times 29.03 appears in the data set, and how many times 29.04 appears, and how many time 29.05 appears, and so on? Explain why you want this. Or maybe you don't want this, you want something else. But please explain what you want. Don't jump to another variable, we are talking about Total_Payment_Amount. 

There are potentially a huge number of values of Total_Payment_Amount and potentially a huge number of values of tx_char_i11, and the resulting table would be huge, so huge that SAS won't display it. Something else might be more meaningful, we could steer you to an analysis that can be displayed by SAS, if only you would explain what you want and why you want it. Please take a step back, and tell us what the goals of this project are.

[Sandeep77]

Hi, Sorry for that, Total_Payment_Amount is what I am referring as collection. I want sas to give me total number of each bureau source (tx_char_i11) and also the total of collections (Total_Payment_Amount ) for each bureau source. I hope that make sense. I don't want the count of any number that x number came so many times but I am looking for the sum of Total_Payment_Amount  for each bureau source. Thanks

[ballardw]

---

@Sandeep77 wrote:

Hi, Sorry for that, Total_Payment_Amount is what I am referring as collection. I want sas to give me total number of each bureau source (tx_char_i11) and also the total of collections (Total_Payment_Amount ) for each bureau source. I hope that make sense. I don't want the count of any number that x number came so many times but I am looking for the sum of Total_Payment_Amount  for each bureau source. Thanks

---

You really should provide some example data. you are asking about a "bureau source" but have not even given us a NAME of the variable.

If you want to both COUNT and SUM the same variable then Proc Freq is not the tool. It does not do sums.

Maybe:

Proc tabulate data=excel_format_3;
   class bureauvariablename;
   var tx_char_i11; /* that is an ugly variable name*/
   table bureauvariablename ,   
             tx_char_i11*(n   sum)
   ;
run;

Proc Tabulate uses the statistic N to request the count of non-missing values, Sum to total them. The variables to be be "totaled" if that means sum or other summary statistic have to be on the VAR statement. Variables used for groups go on CLASS statements.

That comma is a critical piece of the code in tabulate as it separates, in this case, row and column . A third level can create separate tables.

[Tom · Accepted solution]

To summarize data like that use PROC SUMMARY.

proc summary data=HAVE ;
  class tx_char_i11;
  var Total_Payment_Amount ;
  output out=want n=Count sum= ;
run;

If you just want a printout instead of dataset you can add the PRINT option (or use the PROC MEANS alias for the procedure) and list the requested statistics in the PROC statement.

proc means data=HAVE N SUM ;
  class tx_char_i11;
  var Total_Payment_Amount ;
run;

[PaigeMiller]

---

@Sandeep77 wrote:

Hi, Sorry for that, Total_Payment_Amount is what I am referring as collection. I want sas to give me total number of each bureau source (tx_char_i11) and also the total of collections (Total_Payment_Amount ) for each bureau source. I hope that make sense. I don't want the count of any number that x number came so many times but I am looking for the sum of Total_Payment_Amount  for each bureau source. Thanks

---

PROC FREQ does not provide sums. As pointed out by @Tom , you need PROC MEANS or PROC SUMMARY.