Proc TABULATE : FREQ option ignores decimals

[Franck1048]

I'm trying to go from a simple two-way frequency table to a proc tabulate. However my weight value uses decimals (1.0677549165).

This works fine with proc freq :

 data region;
   infile datalines ;
   input flag:$1. year:$4. region:$8.;
   Correction = 79867/74799;
 datalines;
 Y 2018 Region01 
 Y 2017 Region02 
 N 2017 Region01 
 Y 2017 Region01 
 N 2018 Region02 
 Y 2017 Region01 
 Y 2017 Region01
 Y 2017 Region01 
 N 2018 Region01 
 Y 2018 Region01
 Y 2018 Region02 
 N 2018 Region02 
 Y 2017 Region01 
 N 2017 Region02
 Y 2017 Region01 
 N 2018 Region01 
 Y 2018 Region02 
 N 2017 Region01 
 Y 2017 Region01 
 Y 2018 Region01 
 N 2017 Region01
 N 2018 Region01
 N 2017 Region01
 N 2018 Region02
 N 2017 Region02
 N 2018 Region02
 Y 2018 Region02
 Y 2017 Region01
 Y 2018 Region01
 Y 2018 Region01
 ;;;;

 * Flag by region, flag by year;
proc freq data=region;

	table flag * region / nocum norow nopercent;
	table flag * year / nocum norow nopercent;

	weight Correction;

run;

This way I get two tables, one per region and one per year with the weight properly taken into account :

Now I would like to have a single table crossing both region and year. I would do this with proc tabulate :

proc tabulate data=region;
	
	class flag year region;

	table year * flag, region * (N pctn<flag all>) all;

	freq Correction;

run;

The result is not weighted, because the FREQ option removes the decimals of Correction internally, in this case using the value of 1 :

Any idea how I should proceed? Thank you

[Reeza]

Weights apply to variables in PROC TABULATE and you have no analysis variables. 

So you can add a 1 to each record and then do a SUM of that variable with the weights to get the values you want.

I do think you need to be using WEIGHT again though, not PROC FREQ.

See the changes in your code below, highlighted in purple. I did the counts, you can do the percentages :).

data region;
   infile datalines ;
   input flag:$1. year:$4. region:$8.;
   Correction = 79867/74799;
   Indicator=1;
 datalines;
 Y 2018 Region01 
 Y 2017 Region02 
 N 2017 Region01 
 Y 2017 Region01 
 N 2018 Region02 
 Y 2017 Region01 
 Y 2017 Region01
 Y 2017 Region01 
 N 2018 Region01 
 Y 2018 Region01
 Y 2018 Region02 
 N 2018 Region02 
 Y 2017 Region01 
 N 2017 Region02
 Y 2017 Region01 
 N 2018 Region01 
 Y 2018 Region02 
 N 2017 Region01 
 Y 2017 Region01 
 Y 2018 Region01 
 N 2017 Region01
 N 2018 Region01
 N 2017 Region01
 N 2018 Region02
 N 2017 Region02
 N 2018 Region02
 Y 2018 Region02
 Y 2017 Region01
 Y 2018 Region01
 Y 2018 Region01
 ;;;;

 * Flag by region, flag by year;
proc freq data=region;

	table flag * region *year / list nocum norow nopercent;

	weight Correction;

run;

proc tabulate data=region;
	
	class flag year region;
    var indicator;
	table year * flag, region * (indicator*Sum*f=8.2 pctn<flag all>) all;

	weight Correction;

run;

---

@Franck1048 wrote:

I'm trying to go from a simple two-way frequency table to a proc tabulate. However my weight value uses decimals (1.0677549165).

This works fine with proc freq :

 data region;
   infile datalines ;
   input flag:$1. year:$4. region:$8.;
   Correction = 79867/74799;
 datalines;
 Y 2018 Region01 
 Y 2017 Region02 
 N 2017 Region01 
 Y 2017 Region01 
 N 2018 Region02 
 Y 2017 Region01 
 Y 2017 Region01
 Y 2017 Region01 
 N 2018 Region01 
 Y 2018 Region01
 Y 2018 Region02 
 N 2018 Region02 
 Y 2017 Region01 
 N 2017 Region02
 Y 2017 Region01 
 N 2018 Region01 
 Y 2018 Region02 
 N 2017 Region01 
 Y 2017 Region01 
 Y 2018 Region01 
 N 2017 Region01
 N 2018 Region01
 N 2017 Region01
 N 2018 Region02
 N 2017 Region02
 N 2018 Region02
 Y 2018 Region02
 Y 2017 Region01
 Y 2018 Region01
 Y 2018 Region01
 ;;;;

 * Flag by region, flag by year;
proc freq data=region;

	table flag * region / nocum norow nopercent;
	table flag * year / nocum norow nopercent;

	weight Correction;

run;

This way I get two tables, one per region and one per year with the weight properly taken into account :

Now I would like to have a single table crossing both region and year. I would do this with proc tabulate :

proc tabulate data=region;
	
	class flag year region;

	table year * flag, region * (N pctn<flag all>) all;

	freq Correction;

run;

The result is not weighted, because the FREQ option removes the decimals of Correction internally, in this case using the value of 1 :

Any idea how I should proceed? Thank you

---

[Franck1048]

It works with this method, but we don't need the weight statement here, otherwise it applies the weight "twice". So the solution would be :

proc tabulate data=region;
	
	class flag year region;
        var Correction;

	table year * flag, region * (Correction*Sum*f=8.2 pctn<flag all>) all;

run;

The only problem with this table is the Total column, which displays the N. Any idea how to put the total of the sums instead?

Thank you!!

[Reeza]

Modify the ALL to include the SUM rather than N. If no statistic is specified then you get N by default.

[Franck1048 · Accepted solution]

Ok it worked with this :

proc tabulate data=region;
	
	class flag year region;
    var Correction;

	table year * flag, region  * (Correction*Sum*f=8.2 pctn<flag all>) all * (Correction*Sum*f=8.2 pctn<flag all>) ;

run;

That exactly the result I want. Thank you Reeza!