How do i cross just few values of a variable using proc tabulate

[Bedi]

Hi ,

I am trying to cross a few variables but I want to cross just the specific values in those variables.

For Eg. I have a variable

Variable CO_Nike - Value 1 - ' Owners'

                                 Value 2 - 'Nonowner'

Now, I just want to cross the CO_Nike 'Owners' with all my other variables. How can I do this?

Below is the code I have till now , which gives me results for both owners and non-owners

data all_uk_ts;
	input V10101 2. +1 co_nike 1.;
	Datalines; 11 1
11 1
11 2
11 1
11 1
11 2
11 1
11 2
11 2
11 1
12 1
12 2
12 1
12 1
12 2
12 1
12 2
12 1
12 1
;

run;

proc print data=all_uk_ts;
run;

proc format;
  value for_ownership 
    1='Owners'
    2='Nonowners';
  value for_country 
    11='UK'
    12='GE';					 
run;

proc tabulate data=all_uk_ts format=10.0;
  format co_nike for_ownership. v10101 for_country.; 
  class co_nike v10101;
  tables co_nike,v10101;
run;

Below is the output I am getting

 

But I want just the owners row in my output.

[RW9]

Post test data in the form of a datastep.  Show what you want the output to look like.  At a guess add:

data all_uk_ts;
  set all_uk_ts;
run;

proc format;
  value for_ownership 
    1='Owners'
    2='Nonowners';
  value for_country 
    11='UK';					 
run;

proc tabulate data=master.all_uk_ts format=10.0;
  format co_nike for_ownership. v10101 for_country.; 
  where co_nike=1;
  class co_nike v10101;
  tables co_nike,v10101;
run;

Note how I avoid using uppercase, use indents, finish blocks of code etc.  It is important to make the code as readable as possible.

[Bedi]

Hey, Thanks for your comment, I have updated my post, please have look.

[RW9]

And I assume you tried it with the aforementioned where clause?

  where co_nike=1;

[Bedi]

Yes, however that solution will not be of much use as I want to implement this with multiple variables with select values in the row. 

[RW9]

Please post an example of your problem - the whole problem.  What does this mean for instance - "I want to implement this with multiple variables with select values"?

We can only answer the question as it is presented here.

[Bedi]

This is the whole problem which I have explained in detail in my post.

I want to understand how we can display the cross tab of certain values in the variables by using Proc tabulate.

[RW9]

I am trying to cross a few variables but I want to cross just the specific values in those variables.

For Eg. I have a variable

Variable CO_Nike - Value 1 - ' Owners'

                                 Value 2 - 'Nonowner'

Now, I just want to cross the CO_Nike 'Owners' with all my other variables. How can I do this?

Below is the code I have till now , which gives me results for both owners and non-owners

data all_uk_ts; input V10101 2. +1 co_nike 1.;  Datalines; 11 1 11 1 11 2 11 1 11 1 11 2 11 1 11 2 11 2 11 1 12 1 12 2 12 1 12 1 12 2 12 1 12 2 12 1 12 1 ; run; proc print data=all_uk_ts; run; proc format; value for_ownership 1='Owners' 2='Nonowners'; value for_country 11='UK' 12='GE'; run; proc tabulate data=all_uk_ts format=10.0; format co_nike for_ownership. v10101 for_country.; class co_nike v10101; tables co_nike,v10101; run;

Below is the output I am getting

 

But I want just the owners row in my output.

I have bolded, in your post, what the question presented here is.  The answer to which is:

proc tabulate data=all_uk_ts format=10.0;
  format co_nike for_ownership. v10101 for_country.; 
  where co_nike=1;
  class co_nike v10101;
  tables co_nike,v10101;
run;

Which provides as output:

                                          The SAS System         13:16 Wednesday, July 4, 2018   2

                          ----------------------------------------------
                          |                      |       V10101        |
                          |                      |---------------------|
                          |                      |    UK    |    GE    |
                          |                      |----------+----------|
                          |                      |    N     |    N     |
                          |----------------------+----------+----------|
                          |co_nike               |          |          |
                          |----------------------|          |          |
                          |Owners                |         6|         6|
                          ----------------------------------------------

I cannot ascertain anything further I can provide without some explanation of what further is required?

[Bedi]

I thought I did my best to explain my problem but not enough for you. Here , I will give it another shot.

Below is my full code with all the brands like Nike, Adidas etc

data all_uk_ts;
	input V10101 2. +1 co_nike 1. +1 co_Adidas 1. +1 co_Reebok 1. +1 co_Bat 1.;
	Datalines; 11 1 1 2 2
11 1 2 1 1
11 2 2 1 2
11 1 2 1 1
11 1 2 2 1
11 2 1 2 2
11 1 1 1 1
11 2 2 2 2
11 2 2 2 1
11 1 1 1 1
12 1 2 1 1
12 2 2 1 2
12 1 1 1 1
12 1 1 1 1
12 2 1 2 2
12 1 1 1 1
12 2 2 2 2
12 1 1 1 1
12 1 1 1 1
;

run;

proc print data=all_uk_ts;
run;

proc format;
  value for_ownership 
    1='Owners'
    2='Nonowners';
  value for_country 
    11='UK'
    12='GE';					 
run;

proc tabulate data=all_uk_ts format=10.0;
  format co_nike -- co_Bat for_ownership. v10101 for_country.; 
  class co_nike co_Adidas co_Reebok co_Bat v10101;
  tables co_nike co_Adidas co_Reebok co_Bat,v10101;
run;

My Above code gives below output

However , the output that I am looking for is the one without 'Nonowners' rows in the above output.

I hope this explains.

[RW9]

So something like this (note your datalines is incorrect for the first one - it should be a on separate line, as such your numbers are 1 short).  What I do is blank the data I don't want to include, as tabulate does not use that by default:

data all_uk_ts;
  input V10101 2. +1 co_nike 1. +1 co_Adidas 1. +1 co_Reebok 1. +1 co_Bat 1.;
  array c co_:;
  do over c;
    if c=2 then c=.;
  end;
datalines; 
11 1 1 2 2
11 1 2 1 1
11 2 2 1 2
11 1 2 1 1
11 1 2 2 1
11 2 1 2 2
11 1 1 1 1
11 2 2 2 2
11 2 2 2 1
11 1 1 1 1
12 1 2 1 1
12 2 2 1 2
12 1 1 1 1
12 1 1 1 1
12 2 1 2 2
12 1 1 1 1
12 2 2 2 2
12 1 1 1 1
12 1 1 1 1
;

run;

proc format;
  value for_ownership 
    1='Owners'
    2='Nonowners';
  value for_country 
    11='UK'
    12='GE';                
run;

proc tabulate data=all_uk_ts format=10.0;
  format co_: for_ownership. v10101 for_country.; 
  class v10101;
  var co_:;
  tables co_: * (n=''),v10101;  /* You could put the word owners in the label there */
run;

Its a bit of a faff, which is why I really don't like the tabulate procedure.  If I was doing this table I would datastep my results, just a retained count, then transpose that up by country if needed.

[Astounding]

As a general approach ....

Using the PROC TABULATE as is, create an output data set.

Subset the output data set to contain just the data you want displayed.

Feed the subset into a second PROC TABULATE (probably requesting the SUM statistic).