Organizar tabela

[Thalitacosta]

From the code::

proc freq data=base;

by month;

tables type_client*leaf / out=data1;

run;

I created the table below, but I need it to have the following variables: month, leaf, count_type_client (if 0), count_type_client (if 1).

That is, I need to separate the variable "type_client" into two columns, if 0 and if 1.

OUTDATA created:

month	type_client	leaf	count	percent
1	0	3	21188	19.59
1	0	5	25208	23.31
1	0	7	7566	6.99
1	1	3	529	0.49
1	1	5	1066	0.98
1	1	7	522	0.48
2	0	3	23253	16.94
2	0	5	39683	28.91
2	0	7	12354	9.00
2	1	3	632	0.46
2	1	5	2044	1.49
2	1	7	871	0.63

[Reeza]

Do you need counts and percents?

PROC TABULATE (or REPORT) is a better option in this case.
Tutorial on PROC TABULATE - percentages are slightly harder but you also have more control over it.
https://stats.idre.ucla.edu/sas/faq/how-can-i-create-tables-using-proc-tabulate/

[ballardw · Accepted solution]

Your question is not very clear as to whether by "table" you mean the output data set created by proc freq or a report for people to read.

If it is a report then you need to show what the rest of the columns look like because just adding separate columns for type_client doesn't make much sense. What would be the count and percent on a row?

If want a header of the client type with the count and % below then that would be Proc Report or tabulate for the report people read but the data set will quite different.

See if this helps;

Proc tabulate data=base;
   class month leaf type_client;
  table month*leaf,
          type_client*(n reppctn)
  ;
run;

This will have the count and percent under each level of client.

There are a number of different percents that can be calculated, row, column, page (if you specify a page dimension before the row of month and leaf)

[Thalitacosta]

Perfect! The code helped me.

Sorry for the spelling mistakes, I don't know english, I'm Brazilian.

[jimbarbour]

Sorry for the spelling mistakes, I don't know english, I'm Brazilian.

---

In that case, "Bom dia".

Everything now is "Muito bom" yes?  🙂

Jim

[ballardw]

---

@Thalitacosta wrote:
Perfect! The code helped me.

Sorry for the spelling mistakes, I don't know english, I'm Brazilian.

---

Didn't notice any spelling problem, just a partial description of need.

Well done for non-English speaker. I know I don't want to discuss such technical things in any of the other languages I have some experience with.

[Thalitacosta]

I used only N:

Proc tabulate data=base;
class month leaf type_client;
table month*leaf,
type_client*n
;
run;

But now I need to add the total (all) at the end of each line (sum of the observations of 0 and 1). So it looks like this:
type_client
0 1
month leaf N N ALL
1 3 21188 529 21717
1 5 25208 1066 26274

[jimbarbour]

You may want to open a new Question for this.  It will get more attention since this question is already marked as Solved.

Jim

[Reeza]

FYI - if you're not familiar with ODS, you can use it to have the PROC FREQ output displayed transferred directly to Excel/PDF/Word.

ods pdf file='/home/demo/file.pdf' ;
proc freq data=sashelp.class;
table age*sex;
run;
ods pdf close;

[jimbarbour]

Do you want your results to look like this?

If so, the below code would work.  If you want a SAS dataset instead of HTML, then uncomment the CREATE TABLE portion of the code.

DATA	Data1;
	INFILE	DATALINES	DSD	DLM='09'X;
	INPUT
		month	type_client	leaf	count	percent;
DATALINES;
1	0	3	21188	19.59
1	0	5	25208	23.31
1	0	7	7566	6.99
1	1	3	529	0.49
1	1	5	1066	0.98
1	1	7	522	0.48
2	0	3	23253	16.94
2	0	5	39683	28.91
2	0	7	12354	9.00
2	1	3	632	0.46
2	1	5	2044	1.49
2	1	7	871	0.63
;
RUN;

PROC	SQL	;
/*	CREATE	TABLE	Data2	AS*/
	SELECT	
		month	
		,type_client
		,leaf	
		,CASE
			WHEN	(type_client = 0)  THEN	count	
			ELSE	.
			END	AS	Client_0_Count
		,CASE
			WHEN	(type_client = 1)  THEN	count	
			ELSE	.
			END	AS	Client_1_Count
		,percent
		FROM	Data1
		;
QUIT;

Jim