BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Pyrite | Level 9

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

 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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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_=.;

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
NewUsrStat
Pyrite | Level 9
No, how to transpose is also an issue for me. Btw thank you very much!
PaigeMiller
Diamond | Level 26

@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

--
Paige Miller
NewUsrStat
Pyrite | Level 9

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
Super User Tom
Super User

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
Super User

@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
Pyrite | Level 9
Edited successfully for the output
Tom
Super User Tom
Super User

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
Super User Tom
Super User

Seems simple enough with TABULATE.

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

Tom_0-1692372961221.png

 

Tom
Super User Tom
Super User

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
Pyrite | Level 9
Thank you very much Tom! It works perfectly!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 767 views
  • 1 like
  • 4 in conversation