topic Re: Creating three new variables in a panel dataset in New SAS User

Creating three new variables in a panel dataset

AlG — Mon, 20 Jul 2020 22:56:44 GMT

Hi friends,

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.

Re: Creating three new variables in a panel dataset

PGStats — Tue, 21 Jul 2020 02:17:32 GMT

Assuming your data is sorted by brand_name year0 and week :

data want;
set have;
by brand_name;
if first.brand_name then do;
   id + 1;
   weekId = 0;
   end;
weekId + 1;
format percentA percentB ....; /* Removes the format */
run;

Removing the format brings the data representation to its original (fractional) form.

Re: Creating three new variables in a panel dataset

AlG — Tue, 21 Jul 2020 03:55:53 GMT

@PGStats Thanks so much.

The code that you provided elegantly generates the brand_name ID and week ID. However, it does not convert the percentage values to fractional form. For example, I have a variable named "pct_svm_price". Base on your code, I ran the following. However, the format is still percentage. Am I missing something here?

data Final3;
set Final2;
format pct_svm_price; run;

I don't know if this is at all relevant, but as I said before, the percentage variables were originally generated using avg(). For example, pct_svm_price was calculated using the following code:

avg (case when svm_price= "yes" then 1 else 0 end) format=percent7.2 as pct_svm_price

Re: Creating three new variables in a panel dataset

Kurt_Bremser — Tue, 21 Jul 2020 11:02:35 GMT

Pay attention to details. You will find that @PGStats' code explicitly removes the PERCENT format, thus displaying the raw value (which is 0.917 for a value displayed as 91.7%).

Re: Creating three new variables in a panel dataset

AlG — Tue, 21 Jul 2020 12:41:17 GMT

@Kurt_Bremser I am so sorry, I read your message several times and went back to @PGStats 's code but couldn't figure out what is going on. BTW, I don't know if this helps or not but the variable pct_svm_price is character variable.

Re: Creating three new variables in a panel dataset

Kurt_Bremser — Tue, 21 Jul 2020 13:38:06 GMT

Come on, there's even a comment in there that says "Removes the format"!

Re: Creating three new variables in a panel dataset

AlG — Tue, 21 Jul 2020 13:40:15 GMT

@Kurt_Bremser I see that. That's why I did the following:

data Final3;
set Final2;
format pct_svm_price; run;

Am I missing something?

Re: Creating three new variables in a panel dataset

Kurt_Bremser — Tue, 21 Jul 2020 13:45:48 GMT

You want to remove the PERCENT format from the percent.... variables, so you have to name those in the FORMAT statement.

Re: Creating three new variables in a panel dataset

AlG — Tue, 21 Jul 2020 14:02:03 GMT

@Kurt_Bremser

The variable is pct_svm_price, and I am naming it in the format statement (format pct_svm_price;).

Re: Creating three new variables in a panel dataset

Kurt_Bremser — Tue, 21 Jul 2020 15:00:15 GMT

Then please post the complete log of the step that creates this variable.

Use the </> button to post the log.

Re: Creating three new variables in a panel dataset

AlG — Tue, 21 Jul 2020 16:22:22 GMT

@Kurt_Bremser Below is the log

proc sql;
    create table Final as
    select avg (case when svm_price= "yes" then 1 else 0 end) format=percent7.2 as pct_svm_price
from Pre-Final
quit;

Re: Creating three new variables in a panel dataset

PGStats — Tue, 21 Jul 2020 15:48:56 GMT

To get from a character variable to a numeric variable, use the INPUT function with the PERCENT. informat:

data test;
pct_str = "91.7%";
pct_num = input(pct_str, percent.);
run;

proc print data=test; run;

Obs. 	pct_str 	pct_num
1 	91.7% 	0.917

Note that you cannot change the type of a variable, you have to create a new one.

If you simply want to change the string, convert to a number and back to a string, with a different format:

data test;
pct_str = "91.7%";
pct_str = left(put(input(pct_str, percent.), best.));
run;

proc print data=test; run;

Obs. 	pct_str
1 	0.917

Re: Creating three new variables in a panel dataset

Kurt_Bremser — Tue, 21 Jul 2020 16:20:40 GMT

Here you create
pct_svm_brand
which is nowhere in your previous codes.

Re: Creating three new variables in a panel dataset

AlG — Tue, 21 Jul 2020 16:23:13 GMT

@Kurt_Bremser My bad! That was a typo. I corrected it.

Re: Creating three new variables in a panel dataset

Kurt_Bremser — Wed, 22 Jul 2020 08:20:33 GMT

Then the variable pct_sum_price cannot (I repeat: CANNOT) be of type character, and you simply need to omit the format= option in the SQL to get the raw value:

data pre_final;
input svm_price $;
datalines;
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
no
;

proc sql;
create table Final as
  select avg (case when svm_price= "yes" then 1 else 0 end) as pct_svm_price
  from pre_final;
quit;

Result in dataset final:

0.9473684211