proc tabulate data = ghpb22 (where=(filingsize='1'));

class ent3 INS3 / PRELOADFMT;
TITLE 'Table A6. Balance Sheet of Group Health Plans with 100 or More Participants and Trusts';
title2 'by type of insurance and type of plan, 2022';
title3 '(millions)';
var CAST_AST RECEIVABLES_AST GOVT_SEC_AST DEBT_INST_AST STOCK_AST PART_JOINT_INT_AST 
REAL_ESTATE_AST LOANS_AST DFE_AST REG_INVST_CO_AST INS_CO_GEN_AST OTHER_AST TOT_ASSETS_EOY_AME IOH_ASST;
weight dup_wgt;
table (TOT_ASSETS_EOY_AME ='TOTAL ASSETS'
	CAST_AST ='Cash'
	RECEIVABLES_AST='Receivables'
	GOVT_SEC_AST='U.S. Government Securities'
	DEBT_INST_AST='Debt Instruments'
	STOCK_AST='Stock'
	PART_JOINT_INT_AST='Partnership/Joint Venture Interests'
	REAL_ESTATE_AST='Real Estate'
	LOANS_AST='Loans'
	DFE_AST='Assets in Direct Filing Entities'
	REG_INVST_CO_AST='Assets in Registered Investment Companies'
	INS_CO_GEN_AST='Assets in Insurance Co. General Accounts'
	OTHER_AST ='Other')*sum=' '*f=m12f_2stars., 
(all='All Plans' INS3=' ')*(all='Total' ent3=' ')/ Box='Assets' MISSTEXT='-';
run;

data ghpb22_interim;
set ghpb22;
where filingsize='1';
CAST_AST = CAST_AST*DUP_WGT;
RECEIVABLES_AST = RECEIVABLES_AST*DUP_WGT;
GOVT_SEC_AST = GOVT_SEC_AST*DUP_WGT;
DEBT_INST_AST = DEBT_INST_AST*DUP_WGT;
STOCK_AST = STOCK_AST*DUP_WGT;
PART_JOINT_INT_AST = PART_JOINT_INT_AST*DUP_WGT;
REAL_ESTATE_AST = REAL_ESTATE_AST*DUP_WGT;
LOANS_AST = LOANS_AST*DUP_WGT;
DFE_AST = DFE_AST*DUP_WGT;
REG_INVST_CO_AST = REG_INVST_CO_AST*DUP_WGT;
INS_CO_GEN_AST = INS_CO_GEN_AST*DUP_WGT;
OTHER_AST = OTHER_AST*DUP_WGT;
IOH_ASST = IOH_ASST*DUP_WGT;
run;

data ghpb22_A6;
set ghpb22_interim;
keep CAST_AST RECEIVABLES_AST GOVT_SEC_AST DEBT_INST_AST STOCK_AST PART_JOINT_INT_AST 
REAL_ESTATE_AST LOANS_AST DFE_AST REG_INVST_CO_AST INS_CO_GEN_AST OTHER_AST ack_id ins3 ent3;
run;

proc sort data=ghpb22_A6; by ack_id ins3 ent3; run;
proc transpose data=ghpb22_A6 out=ghpb22_A6_long;
	by ack_id ins3 ent3;
run;

data ghpb22_A6_long1;
	set ghpb22_A6_long (rename=(_name_=category col1=assets));
run;

proc format;

picture pctfmt (round fuzz=0) low-high='009%';

value $categoryf (notsorted)
'CAST_AST'='Cash'
'RECEIVABLES_AST'='Receivables'
'GOVT_SEC_AST'='U.S. Government Securities'
'DEBT_INST_AST'='Debt Instruments'
'STOCK_AST'='Stock'
'PART_JOINT_INT_AST'='Partnership/Joint Venture Interests'
'REAL_ESTATE_AST'='Real Estate'
'LOANS_AST'='Loans'
'DFE_AST'='Assets in Direct Filing Entities'
'REG_INVST_CO_AST'='Assets in Registered Investment Companies'
'INS_CO_GEN_AST'='Assets in Insurance Co. General Assets'
'OTHER_AST'='Other'
;

run;

proc tabulate data = ghpb22_A6_long1 order=freq;

class ins3 ent3 / PRELOADFMT;
class category;
format category $categoryf.;
TITLE 'Table A6. Balance Sheet of Group Health Plans with 100 or More Participants and Trusts';
title2 'by type of insurance and type of plan, 2022';
title3 '(millions)';
var assets;
table category=''*colpctsum=' '*assets=''*f=pctfmt., 
(all='All Plans' INS3=' ')*(all='Total' ent3=' ')/ Box='Percentage Distribution of Assets' MISSTEXT='-';
run;
