topic Oneways table in SAS Procedures

Oneways table

brophymj — Wed, 08 Oct 2014 09:40:51 GMT

I have created a oneways table for two factors using the following code:

proc summary data = DATASET nway missing;

class PRODUCT VEHICLE_TYPE;

var VEH_YEARS CLAIM_NOS CLAIM_COST;

ways 1;

output out = ONEWAYS(drop = _freq_ _type_) sum=;

run;

Which produces the dataset:

PRODUCT	VEHICLE_TYPE	VEH_YEARS	CLAIM_NOS	CLAIM_COST
MFF		234	198	1203940
MFA		223	134	776098
	VAN	50	22	200010
	TRUCK	177	150	1200023
	SPECIAL	23	1	12039
	CAR	207	159	567966

What I need to export is a file in the following format:

FACTOR	LEVEL	VEH_YEARS	CLAIM_NOS	CLAIM_COST
PRODUCT	MFF	234	198	1203940
PRODUCT	MFA	223	134	776098
VEHICLE_TYPE	VAN	50	22	200010
VEHICLE_TYPE	TRUCK	177	150	1200023
VEHICLE_TYPE	SPECIAL	23	1	12039

and so on..

I have lots of factors and I'm stuck on a way to achieve this. Any help would be great.

Re: Oneways table

data_null__ — Wed, 08 Oct 2014 10:20:40 GMT

You need PROC SUMMARY option CHARTYPE and CLASS statement option MLF. MLF converts all the CLASS variable to character so you can put them in an ARRAY. The position of the 1 in _TYPE_ is the array index.

%let class=age sex;
proc summary data=sashelp.class chartype; 
   class &class / MLF; 
   ways 1; 
   var height weight;
   output out=oneways sum=;
   run; 
data oneways2;
   length Factor $32 Level $16 /*or more*/; 
   set oneways;
   array _c &class;
   i = findc(_type_,'1'); 
   factor = vname(_c);
   level  = _c;
   drop &class i _type_ _freq_;
   run; 
proc print; 
   run;

Re: Oneways table

Loko — Wed, 08 Oct 2014 10:28:15 GMT

Hello,

One solution:

data have;
input PRODUCT $ VEHICLE_TYPE $ VEH_YEARS CLAIM_NOS CLAIM_COST;
datalines;
MFF . 234 198 1203940
MFA . 223 134 776098
. VAN 50 22 200010
. TRUCK 177 150 1200023
. SPECIAL 23 1 12039
. CAR 207 159 567966
;

data want;

length factor level $20 ;
set have;

if missing(VEHICLE_TYPE) then
do;
level=product;
factor=vname(product);
end;
else if missing(product) then
do;
level=VEHICLE_TYPE;
factor=vname(VEHICLE_TYPE);
end;

drop PRODUCT VEHICLE_TYPE;
run;

Re: Oneways table

brophymj — Wed, 08 Oct 2014 10:48:36 GMT

Many thanks data_null

That works great. One other feature i wanted in the table was a % split of total for each level for each factor

So this column would look like:

Factor Level %SPLIT

Product MFF 51.2%

Procuct MFA 49.8%

...

So for each factor, the % split of each level would be given.

Re: Oneways table

data_null__ — Wed, 08 Oct 2014 11:23:35 GMT

%let class=age sex;
proc summary data=sashelp.class chartype descendtypes; 
   class &class / MLF; 
   ways 1; 
   var height weight;
   output out=oneways sum=;
   run; 
data oneways2;
   length Factor $32 Level $16 /*or more*/; 
   set oneways;
   array _c &class;
   i = findc(_type_,'1'); 
   factor = vname(_c);
   level  = _c;
   drop &class i _type_;
   run; 
proc freq data=oneways2 noprint; 
   by factor notsorted; 
   tables level / out=splits(index=(splits=(factor level)));
   weight _freq_;
   run; 
data oneways3;
   set oneways2;
   set splits key=splits/unique; 
   run; 
proc print; 
   run; 