topic Re: How do i cross just few values of a variable using proc tabulate in ODS and Base Reporting

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

Bedi — Wed, 04 Jul 2018 10:51:09 GMT

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.

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

RW9 — Wed, 04 Jul 2018 10:21:19 GMT

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.

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

Bedi — Wed, 04 Jul 2018 10:52:26 GMT

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

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

RW9 — Wed, 04 Jul 2018 12:12:46 GMT

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

  where co_nike=1;

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

Bedi — Wed, 04 Jul 2018 12:39:27 GMT

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.

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

RW9 — Wed, 04 Jul 2018 12:44:59 GMT

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.

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

Bedi — Wed, 04 Jul 2018 13:09:46 GMT

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.

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

RW9 — Wed, 04 Jul 2018 13:22:03 GMT

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?

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

Astounding — Wed, 04 Jul 2018 16:58:43 GMT

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).

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

Bedi — Thu, 05 Jul 2018 06:06:53 GMT

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.

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

RW9 — Thu, 05 Jul 2018 08:30:46 GMT

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.