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.
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.
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.
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.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.