Creating three new variables in a panel ?

[AntonioFinn]

I have a panel dataset which includes weekly data for several brands across four years (250 observations per brand). I appreciate if you help me with doing the following three things (I apologize in advance if I shouldn't ask multiple questions under one topic). Also sorry I cannot share a sample of my data due to NDA.

1. My dataset includes a column named "brand_name". I want to create a new column ("id") which uniquely identifies each brand. So it should take on the same value for all the 250 rows associated with each brand.

2. I need a new variable which assigns a number (from 1 to 250) to each observation for each brand. My dataset includes two columns "Year0" and "week". For each brand, my data starts from the 6th week of 2014 up until 52nd week of 2014. Then, from week 0 of 2015 to week 52 of 2015, and so on. In other words, when "Year0" is 2014, "week" starts from 6 all the way to 52. Then for the next row, in which "Year0" is 2015, "week" is 0 (so the variable week starts over with a change in Year0). The new variable should assign 1 whenever Year0 is 2014 and week is 6, and continue this until the last observation, for which it should be 250.

3. I have several variables which are in percentage (they have been calculated using avg() format=percent7.2 ). I need to convert these to numbers. so for example 91.70% should be converted to 0.917.

Thanks so much in advance for your help.

0 Likes

[AntonioFinn]

I have a panel dataset which includes weekly data for several brands across four years (250 observations per brand). I appreciate if you help me with doing the following three things (I apologize in advance if I shouldn't ask multiple questions under one topic). Also sorry I cannot share a sample of my data due to NDA.

1. My dataset includes a column named "brand_name". I want to create a new column ("id") which uniquely identifies each brand. So it should take on the same value for all the 250 rows associated with each brand.

2. I need a new variable which assigns a number (from 1 to 250) to each observation for each brand. My dataset includes two columns "Year0" and "week". For each brand, my data starts from the 6th week of 2014 up until 52nd week of 2014. Then, from week 0 of 2015 to week 52 of 2015, and so on. In other words, when "Year0" is 2014, "week" starts from 6 all the way to 52. Then for the next row, in which "Year0" is 2015, "week" is 0 (so the variable week starts over with a change in Year0). The new variable should assign 1 whenever Year0 is 2014 and week is 6, and continue this until the last observation, for which it should be 250.

3. I have several variables which are in percentage (they have been calculated using avg() format=percent7.2 ). I need to convert these to numbers. so for example 91.70% should be converted to 0.917.

Thanks so much in advance for your help.

[Kurt_Bremser]

Sort by brand, year and week.

Then run a data step:

data want;
set have;
by brand;
retain id count 0;
if first.brand
then do;
  id + 1;
  count = 1;
end;
else count + 1;
run;

To display the percent value in its raw form, just change the format to a "normal" one like 8.3.

[andreas_lds]

---

@AntonioFinn wrote:

3. I have several variables which are in percentage (they have been calculated using avg() format=percent7.2 ). I need to convert these to numbers. so for example 91.70% should be converted to 0.917.

Thanks so much in advance for your help.

---

once more: a format only changes how a variable is displayed, not the value of the variable.So removing the format from the variable should solve that issue.

[andreas_lds]

3. I have several variables which are in percentage (they have been calculated using avg() format=percent7.2 ). I need to convert these to numbers. so for example 91.70% should be converted to 0.917.

Thanks so much in advance for your help.