Creating new variables based on value labels of another variable

[Ketty Noonan-2]

Hi everyone,

I have a variable called Product, which has N values:
1 'Product A'
2 'Product B'
3 'Product C'
.
N 'Product N'.

How can I create N new variables whose names are the value labels of Product?

Therefore, the new variables will be called product_A, product_B, etc.

Thanks!
Ketty

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+


	
	
	
	

[Bruce Weaver]

You've not said what you want the values of the new variables to be.  Presumably you want N indicator (or dummy) variables, is that right?  

Do you have to have letters (A, B, C, ...N) as suffixes for the new variable names?  I ask, because it would be easier if you could settle for numeric suffixes (Product_1, Product_2, ... Product_N).  

Ketty Noonan-2 wrote

Hi everyone,

I have a variable called Product, which has N values:
1 'Product A'
2 'Product B'
3 'Product C'
.
N 'Product N'.

How can I create N new variables whose names are the value labels of
Product?

Therefore, the new variables will be called product_A, product_B, etc.

Thanks!
Ketty


+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+

[David Marso]

Use VALUELABEL function to create a new string variable, fix any spaces with REPLACE, then use that as INDEX in CASESTOVARS.

Ketty Noonan-2 wrote

Hi everyone,

I have a variable called Product, which has N values:
1 'Product A'
2 'Product B'
3 'Product C'
.
N 'Product N'.

How can I create N new variables whose names are the value labels of
Product?

Therefore, the new variables will be called product_A, product_B, etc.

Thanks!
Ketty


+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+

[Bruce Weaver]

Off-list message from Ketty, whose response to the list generated an error message.  Just found this in my hotmail inbox, which I do not check too frequently (as per the note in my sig file below).  

I don't have time to look at it right now, but maybe someone else will jump in meanwhile.


--- Start of response from Ketty ---
Yes, I want N indicator (or dummy) variables. One variable for each
product/brand/category, etc.

An example of the dataset

id  Group1_buyer  Group2_buyer     Group3_buyer       Product   Quantity
1                     New            Lost                 PL       1
1                     New            Lost              Brand A     0
1                     New            Lost              Brand B     0

In this case, I'd like to rename Group1_buyer to reflect PL, Group2_buyer
to reflect Brand A and Group3_buyer to reflect Brand B.

The challenge is my syntax needs to be general. Product is not always PL,
Brand A and Brand B. In a different database, it can be "Perfume",
"Splash", etc.

I really appreciate any help. Thanks!

--- End of response from Ketty ---

Bruce Weaver wrote

You've not said what you want the values of the new variables to be.  Presumably you want N indicator (or dummy) variables, is that right?  

Do you have to have letters (A, B, C, ...N) as suffixes for the new variable names?  I ask, because it would be easier if you could settle for numeric suffixes (Product_1, Product_2, ... Product_N).  

Ketty Noonan-2 wrote

Hi everyone,

I have a variable called Product, which has N values:
1 'Product A'
2 'Product B'
3 'Product C'
.
N 'Product N'.

How can I create N new variables whose names are the value labels of
Product?

Therefore, the new variables will be called product_A, product_B, etc.

Thanks!
Ketty


+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+

[David Marso]

Wouldn't it be grand to see what the resulting data should look like?
And I have having a terrible time parsing the following into any meaningful code.
OTOH:  Here is a way to turn value labels into varnames using the outline from my previous response.
DATA LIST FREE / Product other.
BEGIN DATA
1 3
2 6
3 4
END DATA.

VALUE LABELS Product
  0 'Product 0'
  1 'Product A'
  2 'Product B'
  3 'Product C' .
COMPUTE ID=$CASENUM.
STRING lab (A20).
COMPUTE lab=REPLACE(VALUELABEL(Product)," ","_").
MATCH FILES / FILE * / KEEP ID product lab.

CASESTOVARS ID=ID /AUTOFIX=NO / INDEX=lab .

LIST.
 
      ID Product_A Product_B Product_C
 
    1.00      1.00       .         .
    2.00       .        2.00       .
    3.00       .         .        3.00
 
 
Number of cases read:  3    Number of cases listed:  3

Bruce Weaver wrote

Off-list message from Ketty, whose response to the list generated an error message.  Just found this in my hotmail inbox, which I do not check too frequently (as per the note in my sig file below).  

I don't have time to look at it right now, but maybe someone else will jump in meanwhile.


--- Start of response from Ketty ---
Yes, I want N indicator (or dummy) variables. One variable for each
product/brand/category, etc.

An example of the dataset

id  Group1_buyer  Group2_buyer     Group3_buyer       Product   Quantity
1                     New            Lost                 PL       1
1                     New            Lost              Brand A     0
1                     New            Lost              Brand B     0

In this case, I'd like to rename Group1_buyer to reflect PL, Group2_buyer
to reflect Brand A and Group3_buyer to reflect Brand B.

The challenge is my syntax needs to be general. Product is not always PL,
Brand A and Brand B. In a different database, it can be "Perfume",
"Splash", etc.

I really appreciate any help. Thanks!

--- End of response from Ketty ---

Bruce Weaver wrote

You've not said what you want the values of the new variables to be.  Presumably you want N indicator (or dummy) variables, is that right?  

Do you have to have letters (A, B, C, ...N) as suffixes for the new variable names?  I ask, because it would be easier if you could settle for numeric suffixes (Product_1, Product_2, ... Product_N).  

Ketty Noonan-2 wrote

Hi everyone,

I have a variable called Product, which has N values:
1 'Product A'
2 'Product B'
3 'Product C'
.
N 'Product N'.

How can I create N new variables whose names are the value labels of
Product?

Therefore, the new variables will be called product_A, product_B, etc.

Thanks!
Ketty


+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+

[Ketty Noonan-2]

Let me be more clear. Here is my dataset.


DATA LIST  LIST
/ id product  quantity * group1(A15) group2(A15) group3(A15).
BEGIN DATA.
1, 1, 1, "Retained","",""
1, 2, 0, "Retained","",""
1, 3, 0, "Retained","",""
2,1,0,"Lost","Lost",""
2,2,0,"Lost","Lost",""
2,3,0,"Lost","Lost",""

END DATA.

LIST.

value labels product
1 'Brand A'
2 'Brand B'
3 'Private Label'.

I am trying to rename group1 to Brand_A, group2 to Brand_B and group3 t=
o
Private_Label. What is the best way to do this?

David, thanks for your help. It's close but I still need to rename grou=
p1
to Brand_A, and so on.

Thanks, everyone!

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+


	
	
	
	

[David Marso]

Wouldn't it be grand to see what the resulting data should look like?

Ketty Noonan-2 wrote

Let me be more clear. Here is my dataset.


DATA LIST  LIST
/ id product  quantity * group1(A15) group2(A15) group3(A15).
BEGIN DATA.
1, 1,            1, "Retained","",""
1, 2,            0, "Retained","",""
1, 3, 0, "Retained","",""
2,1,0,"Lost","Lost",""
2,2,0,"Lost","Lost",""
2,3,0,"Lost","Lost",""

END DATA.

LIST.

value labels product
1 'Brand A'
2 'Brand B'
3 'Private Label'.

I am trying to rename group1 to Brand_A, group2 to Brand_B and group3 t=
o
Private_Label. What is the best way to do this?

David, thanks for your help. It's close but I still need to rename grou=
p1
to Brand_A, and so on.

Thanks, everyone!

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+

[Ketty Noonan-2]

The resulting data looks the same. The only changes are the variable name group1 is now Brand_A, group2 is now Brand_B and group3 is now Private_Label.

DATA LIST  LIST
/ id product  quantity * Brand_A(A15) Brand_B(A15) Private_Label(A15).
BEGIN DATA.
1, 1,            1, "Retained","",""
1, 2,            0, "Retained","",""
1, 3, 0, "Retained","",""
2,1,0,"Lost","Lost",""
2,2,0,"Lost","Lost",""
2,3,0,"Lost","Lost",""

Thank you!

ORIGINAL QUESTION:

This is my original dataset.

DATA LIST  LIST
/ id product  quantity * group1(A15) group2(A15) group3(A15).
BEGIN DATA.
1, 1,            1, "Retained","",""
1, 2,            0, "Retained","",""
1, 3, 0, "Retained","",""
2,1,0,"Lost","Lost",""
2,2,0,"Lost","Lost",""
2,3,0,"Lost","Lost",""

END DATA.

LIST.

value labels product
1 'Brand A'
2 'Brand B'
3 'Private Label'.

I am trying to rename group1 to Brand_A, group2 to Brand_B and group3 to Private_Label. What is the best way to do this?

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+


	
	
	
	

[David Marso]

AFAICT, the question as presented makes absolutely no sense .
1.  You probably DON'T have only 3 'groups'.
2.  There are multiple responses for some cases?.
---

Ketty Noonan-2 wrote

The resulting data looks the same. The only changes are the variable name
group1 is now Brand_A, group2 is now Brand_B and group3 is now
Private_Label.

DATA LIST  LIST
 / id product  quantity * Brand_A(A15) Brand_B(A15) Private_Label(A15).
 BEGIN DATA.
 1, 1,            1, "Retained","",""
 1, 2,            0, "Retained","",""
 1, 3, 0, "Retained","",""
 2,1,0,"Lost","Lost",""
 2,2,0,"Lost","Lost",""
 2,3,0,"Lost","Lost",""

Thank you!

ORIGINAL QUESTION:

This is my original dataset.

DATA LIST  LIST
 / id product  quantity * group1(A15) group2(A15) group3(A15).
 BEGIN DATA.
 1, 1,            1, "Retained","",""
 1, 2,            0, "Retained","",""
 1, 3, 0, "Retained","",""
 2,1,0,"Lost","Lost",""
 2,2,0,"Lost","Lost",""
 2,3,0,"Lost","Lost",""

 END DATA.

 LIST.

 value labels product
 1 'Brand A'
 2 'Brand B'
 3 'Private Label'.

 I am trying to rename group1 to Brand_A, group2 to Brand_B and group3 to
Private_Label. What is the best way to do this?

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+

[Ketty Noonan-2]

1. Yes, I might have more than 3 groups. The number of group varies depending on the dataset.
2. The cases are different for each ID. There are 3 rows for each ID. Each row indicates the quantity purchased for each product (in this dataset: Brand A, Brand B and Private Label).
For example:  ID=1 is a retained buyer for Brand A. Therefore, the quantity for row 1 is 1 and quantity for row 2 and 3 are 0.
3. The goal is to run the custom table (shown below). So each brand, I want to understand what brands the retained, lost and new buyer buy.

Thank you,
Ketty 

DATA LIST  LIST
  / id product  quantity * Brand_A(A15) Brand_B(A15) Private_Label(A15).
  BEGIN DATA.

  1, 1,            1, "Retained","Lost","Lost"
  1, 2,            0, "Retained","Lost","Lost"
  1, 3, 0, "Retained","Lost","Lost"
3,1,2,"Retained","New","Lost"
3,2,3,"Retained","New","Lost"
3,3,0,"Retained","New","Lost"
4,1,2,"New","New","New"
4,2,8,"New","New","New"
4,3,10,"New","New","New"
5,1,0,"Lost","Retained","Retained"
5,2,2,"Lost","Retained","Retained"
5,3,9,"Lost","Retained","Retained"
END DATA.

 LIST.

value label product 

1 'Brand A'
2 'Brand B'
3 'Private Label'.
exe.


ctables
/table product[C]>quantity[S][sum,colpct.sum] by (brand_A[c]+brand_b[c]+private_label[c]).

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+


	
	
	
	

[David Marso]

I would scratch the rename variables idea and simply do a VARSTOCASES.
Rewrite your CTABLES syntax to work with the modified data structure.
I'm done.
---

Ketty Noonan-2 wrote

1. Yes, I might have more than 3 groups. The number of group varies
depending on the dataset.
2. The cases are different for each ID. There are 3 rows for each ID. Each
row indicates the quantity purchased for each product (in this dataset:
Brand A, Brand B and Private Label).
For example:  ID=1 is a retained buyer for Brand A. Therefore, the quantity
for row 1 is 1 and quantity for row 2 and 3 are 0.
3. The goal is to run the custom table (shown below). So each brand, I want
to understand what brands the retained, lost and new buyer buy.

Thank you,
Ketty


  DATA LIST  LIST
    / id product  quantity * Brand_A(A15) Brand_B(A15) Private_Label(A15).
    BEGIN DATA.


    1, 1,            1, "Retained","Lost","Lost"
    1, 2,            0, "Retained","Lost","Lost"
    1, 3, 0, "Retained","Lost","Lost"
  3,1,2,"Retained","New","Lost"
  3,2,3,"Retained","New","Lost"
  3,3,0,"Retained","New","Lost"
  4,1,2,"New","New","New"
  4,2,8,"New","New","New"
  4,3,10,"New","New","New"
  5,1,0,"Lost","Retained","Retained"
  5,2,2,"Lost","Retained","Retained"
  5,3,9,"Lost","Retained","Retained"
  END DATA.

   LIST.

  value label product

  1 'Brand A'
  2 'Brand B'
  3 'Private Label'.
  exe.


  ctables
  /table product[C]>quantity[S][sum,colpct.sum] by (brand_A[c]+brand_b
  [c]+private_label[c]).

+========================================================================+
This email is confidential and may be privileged. If you have received it
in error, please notify us immediately and then delete it.  Please do not
copy it, disclose its contents or use it for any purpose.
+========================================================================+