Proc freq and transpose with multiple classes and variables

[NewUsrStat]

Hi guys,  suppose to have the following: 

class1       class2      region      weightclass       sex        
  0             1          0            1               M      
  0             0          0            3               F
  1             1          1            2               M
  0             0          0            3               F
  1             1          0            2               M

I  would like to do proc freq of weightclass and sex by class1, class2 and region and then I would like to transpose. With a single variable I do the following:

proc sort data=mydata;
             by class1 class2 region weightclass;
              proc freq noprint data=mydata;
             by class1 class2 region;
              table weightclass/ out=freqs;
              proc transpose data=freqs out=db(drop=_name_ _label_) prefix=var_;
              by class1 class2 region;
              id weightclass;
      var count;
run;         

How would it become if I add sex together with weightclass? I don't want the cross-product (weigtclass*sex) but only stratifications by class1 class2 region.

Thank you in advance

Edit: with only class1 (to simplify) desired output: 

Region	class1	weightclass1	weightclass2	weightclass3	sexM	sexF
0	0	1	na	2	1	2
1	0	na	1	na	1	na
0	1	na	na	na	na	na
1	1	na	1	na	1	na

 Note that, for weightclass3 as well as for sexF, 2 is because there are two records corresponding to region= 0 and class1 = 0

[PaigeMiller]

proc freq noprint data=mydata;
    by class1 class2 region;
    table weightclass/out=freqs1;
    table sex/out=freqs2;
run;

I assume you know how transpose these two data sets.

[NewUsrStat]

No, how to transpose is also an issue for me. Btw thank you very much!

[PaigeMiller]

---

@NewUsrStat wrote:
No, how to transpose is also an issue for me. Btw thank you very much!

---

since you don't state what the desired result of this transpose is, we need you to provide a clear explanation of what you want and what the desired output is

[NewUsrStat]

Ok I will edit soon

[NewUsrStat]

I'm not able to edit I don't know why. Btw
regarding proc transpose I just need to add together with id "weightclass", the id "sex". I tried simply adding the word but it doesn't work. Is there a way to do proc transpose one time for both variables that will be concatenated by columns? Let say: if I run the code for one variable it runs perfectly. I just want to run it for two variables without writing the code two times.

[Tom]

Just make a new post with the expected output for the input you posted.

Here is the data you posted before:

data have;
  input class1 class2 region weightclass sex $;
cards;
0 1 0 1 M
0 0 0 3 F
1 1 1 2 M
0 0 0 3 F
1 1 0 2 M
;

You can use the same type of simple data step to post the output you want.

[ballardw]

---

@NewUsrStat wrote:

I'm not able to edit I don't know why. Btw
regarding proc transpose I just need to add together with id "weightclass", the id "sex". I tried simply adding the word but it doesn't work. Is there a way to do proc transpose one time for both variables that will be concatenated by columns? Let say: if I run the code for one variable it runs perfectly. I just want to run it for two variables without writing the code two times.

---

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

[NewUsrStat]

Edited successfully for the output

[Tom]

I cannot tell what output you want from that input.

I am not sure I get the distinct between a cross product and a stratification when talking about PROC FREQ.

Perhaps you should switch to PROC MEANS?  That can also count and you have full control over the combinations of class variables.  To get all combinations of variables try:

data have;
  input class1 class2 region weightclass sex $;
cards;
0 1 0 1 M
0 0 0 3 F
1 1 1 2 M
0 0 0 3 F
1 1 0 2 M
;

proc summary data=have chartype ;
  class _all_;
  output out=ways;
run;

proc print;
run;

You can either filter by the _TYPE_ variable or use the WAYS or TYPES statement to limit the combinations that are produced.

[Tom]

Seems simple enough with TABULATE.

proc tabulate data=have;
 class class1 class2 region weightclass sex ;
 table region*class1
     , weightclass*N sex*N
 ;
run;

[Tom · Accepted solution]

You can use PROC SUMMARY.

Let's introduce some macro variables to give you more flexibility.

One to have the list of grouping variables and a second to have the list of analysis variables.

%let groups=region class1;
%let vars=sex weightclass ;

Now we can use those to generate a PROC SUMMARY call.

proc summary data=have chartype completetypes;
 class &vars &groups ;
 types %sysfunc(translate(&groups,*,%str( )))*(&vars);
 output out=summary;
run;

We can process that output to generate some variable names.

data for_transpose;
  set summary;
  index=indexc(_type_,'1');
  length _name_ $32;
  _name_=scan("&vars",index,' ');
  _name_=catx('_',_name_,vvaluex(_name_));
run;

Now we can transpose that.

proc sort;
  by &groups _type_;
run;

proc transpose data=for_transpose out=want(drop=_name_ );
  by &groups;
  id _name_;
  var _freq_;
run;

Result

                           weightclass_    weightclass_    weightclass_
Obs    region    class1          1               2               3         sex_F    sex_M

 1        0         0            1               0               2           2        1
 2        0         1            0               1               0           0        1
 3        1         0            0               0               0           0        0
 4        1         1            0               1               0           0        1

If you really want missing values instead of zeros then add this line to the FOR_TRANSPOSE data step.

  if _freq_=0 then _freq_=.;

[NewUsrStat]

Thank you very much Tom! It works perfectly!