BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8


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:

PRODUCTVEHICLE_TYPEVEH_YEARSCLAIM_NOSCLAIM_COST
MFF2341981203940
MFA223134776098
VAN5022200010
TRUCK1771501200023
SPECIAL23112039
CAR207159567966

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

FACTORLEVELVEH_YEARSCLAIM_NOSCLAIM_COST
PRODUCTMFF2341981203940
PRODUCTMFA223134776098
VEHICLE_TYPEVAN5022200010
VEHICLE_TYPETRUCK1771501200023
VEHICLE_TYPESPECIAL23112039

and so on..

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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;

    10-8-2014 5-17-55 AM.png

    View solution in original post

    4 REPLIES 4
    data_null__
    Jade | Level 19

    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;

    10-8-2014 5-17-55 AM.png

    brophymj
    Quartz | Level 8

    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.

    data_null__
    Jade | Level 19
    %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;
    10-8-2014 6-21-36 AM.png
    Loko
    Barite | Level 11

    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;

    sas-innovate-2024.png

    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.

     

    Register now!

    What is Bayesian Analysis?

    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.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 4 replies
    • 802 views
    • 0 likes
    • 3 in conversation