Requesting help on renaming variables

[Dhammika Siriwardhana]

Hello,
I reshaped a long-format dataset to a wide format. The Stata command has automatically added the follow-up number at the end of each new variable. I used foreach loop to add a pre-fix as Q1, Q2, Q3 for each created follow-up variable. I now need to remove the follow-up number added by Stata (last digit of each variable). However, I am uncertain how to do it, for example, I cannot use rename *3 * command to rename follow-up 3 variables after renaming follow-up 1 and 2. I would very much appreciate your help.

See some of my variables below.

Follow up 1 (Q1) Q1_S4d_1_11 Q1_S4d_1_21 Q1_S4d_1_31 Q1_S4d_1_41 Q1_S4d_1_51 Q1_S4d_1_61


Follow up 2 (Q2) Q2_S4d_1_12 Q2_S4d_1_22 Q2_S4d_1_32 Q2_S4d_1_42 Q2_S4d_1_52 Q2_S4d_1_62


Follow up 3 (Q3) Q3_S4d_1_13 Q3_S4d_1_23 Q3_S4d_1_33 Q3_S4d_1_43 Q3_S4d_1_53 Q3_S4d_1_63

I look forward to receiving your insights.

Thank you.

Dham

[Maarten Buis]

Are you sure you need your data in wide format? That is a very common mistake to make. Almost everything is easier in long format.

[Krishanu Karmakar]

Will something like this work?

foreach num of numlist 11 12 13 21 22 23 31 32 33 {
               local val = floor(`num'/10)
               rename *`num' *`val'
}

You have to add the other numbers in the numlist.

[Dhammika Siriwardhana]

Are you sure you need your data in wide format? That is a very common mistake to make. Almost everything is easier in long format.

Thanks Maarten, yes I need data in wide format.

[Dhammika Siriwardhana]

Will something like this work?

foreach num of numlist 11 12 13 21 22 23 31 32 33 {
local val = floor(`num'/10)
rename *`num' *`val'
}

You have to add the other numbers in the numlist.

Thanks Krishanu for your reply. Let me have a look.

[Nick Cox]

Thanks Maarten, yes I need data in wide format.

I can't recall a panel data problem easier approached that way.

[Dhammika Siriwardhana]

I can't recall a panel data problem easier approached that way.

Thanks Nick, please let me know if you have any suggestions.

[Dhammika Siriwardhana]

Thanks Krishanu for your reply. Let me have a look.

Hi Krishanu, this worked. I changed numlist as 1, 2, 3, 4 to indicate follow-up number. Then the command you suggested allocated 0 as last digit to all variables in the dataset (replacing the follow-up number 1, 2, 3, 4). Then I managed to rename all variables in one go with the rename *0 * command. Really appreciate your insight. Thanks again.

[Nick Cox]

My obstinate suggestion is that you're better off with long layout.

[Maarten Buis]

My obstinate suggestion is that you're better off with long layout.

The problem we have is we don't know what you want to do. All we know, is that (almost) everything is easier in long format, and that a lot of beginning Stata users (or experienced Stata users who are just starting with panel data) make the mistake you are making. So if you tell us why you think you need wide format, then we can tell you how to do what you want to do easier. Till than, all we can tell you is use long format.

[Krishanu Karmakar]

Hi Krishanu, this worked. I changed numlist as 1, 2, 3, 4 to indicate follow-up number. Then the command you suggested allocated 0 as last digit to all variables in the dataset (replacing the follow-up number 1, 2, 3, 4). Then I managed to rename all variables in one go with the rename *0 * command. Really appreciate your insight. Thanks again.

I assumed that you wanted the following: if the last two digits are 11 12 13 etc. then they should become all 1. If the last two digits are 21, 22, 23 then they should become 2. And that is what was the rationale behind my loop.

If you keep numlist at two digit numbers (which are the last two digits in your variable names after the last underscore) then the number won't be replaced by 0. The first line inside the loop is doing a calculation for each number of the numlist. If all the numbers in the numlist are one digit numbers then the floor(number/10) function changes all of them to 0. But if the numebrs are 11 12 13 21 22 23 etc. then for the first three numbers the (11 12 13) the calculation results in 1, for the next three numbers (21 22 23) the calculation results in 2. Then that 1 is used to replace 11, 12, 13 and 2 is used to replace 21, 22, 23 and so on. May be my interpretation was wrong. Anyway, if it worked for you in a modified format then great.