Using observation as Command

[Hans Iger]

Hello!

I have a feeling this question is a bit odd (and originates from me being an Excel user primarily - shame on me), but anyways:

In order to do corrections of my dataset, I have written down a number of commands manually in a file somewhat like this:

i command

2 command1
4 command2
5 command3
7 command4
9 command5

Where i is an identifier that creates a link to my original dataset.

I would now like to create a function that allows me to loop through consecutive values of, let's say x, and if the value of x equals the value of i, it should execute the command.
So something like:

forvalues x = 1(1)10 {
    if `x' = i[`x'] {
    command[`x']
    }
}

Which of course does not work, because it will return an "invalid command" error.

I hope this is not too far off, but in my particular case it would be extremely helpful, because the commands are actually SQL requests which pull a dataset and in order to save this correctly I would like to use local macros I set up earlier in my code.

Looking forward to hear your ideas!

[Clyde Schechter]

I think you need to explain a bit more about what you are trying to do here. As you have it set up, your -if- command looks at the value of variable i in the x'th observation (x varing from 1 to 10) and if the value of i in the x'th observation is x, then you want to execute the command that is spelled out in the x'th row of variable command. Is that what you want? In the example data you give, this means that none of these commands will ever be executed. (When x = 1, the value of i in the first observation is 2, not 1. When x = 2, the value of i in the second observation is 4, not 2. etc. There is never a match between the value of i in the x'th row and x in your example.)

Also, what you show is the data in memory, so when you finally get one of these commands to run, it will be applying to this data set itself--yet this data looks very unlike the stuff that a SQL command would normally be applied to. So is there some other data set that you want the SQL commands applied to?

[Hans Iger]

Thanks for your response. Sorry for the bad explanation.


Yes! I just noticed that my example will not work, sorry about that. I made some changes to my data to make the whole thing a bit easier. The observations in the variable command are "odbc load" commands to download data (and those commands take SQL form but do not apply to any locally saved data). This data I want to save with a name based on the value of i in the specific row (e.g. very simply: "a2.dta" for the first observation of i with "a" added to have a non-numeric start of the name).

So this is what I want to do:
Loop through the observations of the "command" variable, execute the observation as a command and then save the imported file based on the value of i in the respective row. Of course I have to use the original dataset again at the beginning of the loop because it will be dropped when using odbc load.

The crucial part is getting the observations of the command variable to work as an actual command in Stata, if this is possible.

I hope I have not made it any more complicated.

[daniel klein]

Is there any reason not to write the commands into a do-file and just do (run) that file?

command1
save based on 2
command2
save based on 4
...

I do not get why you would want to read commands from an external (Excel) file. Perhaps I am missing something.

Edit: The above approach will also be much simpler to read and follow. If I were to make (more or less) ad-hoc manual changes to datasets, I would not want to set up a complicated loop for mapping different commands to different (parts of?) datasets that will later take me quite some time to figure out which changes have been made to which dataset.

Best
Daniel

[Hans Iger]

Yes, in hindsight you are definitely correct! Problem is that I come from a background where I am always used to creating back-up Excel files in which I document stuff. In this case the code is supposed to re-download specific series of data, which I documented as erratic in my back-up file and there I also wrote down the necessary odbc command. - I know this is definitely not a smooth thing to do for experienced Stata users. However, in my real file I have over 100 commands to run, and I would now find it quite cumbersome to write those into a do-file and manually add the necessary filename to each single of them. That's why I thought it might be worth giving a shot to try and run the commands written down in my file. Next time I will be smarter to do it immediately in Stata

[daniel klein]

I am still having problems to fully grasp your setup here, but you can use Stata (or another programming tool) to write the respective do-file for you; this is what I would do. If you can create a minimal example of, say, 3 entries in your excel file, we might come up with more specific code suggestions. Start with something along the lines

import excel youfile , clear
dataex in 1/3

to give us something more closely to the real problem to play around with that does not have this stylized command# to it. Also please show us the complete series of commands that you want executed inside the loop that you are aiming at, i.e. the loading, saving and whatever else you want that is not command#.

Best
Daniel

[Hans Iger]

Yes I have been using the do-files for the rest of my code. My excel file looks like the example I posted above.

i command

2 command1
4 command2
5 command3
7 command4
9 command5

In reality, those "command#"s are all "odbc load" type commands, which of course only work with my odbc driver set-up so I replaced them with stylized entries.
The odbc load command will clear the current memory and import a dataset with 2 variables with a few observations each.

My entire loop should look somewhat like this:

forvalues x = 1(1)10 {

import excel using /* [file of structure as in example above] */, firstrow clear

/*  Execute Command[`x']  */

save "a`x'.dta", replace

}

[daniel klein]

Import your Excel file to get something like this

     +----------------------+
     | i            command |
     |----------------------|
  1. | 2   describe , short |
  2. | 4          summarize |
     +----------------------+

For those who want to reproduce in Stata

clear
input i str244 command
2 "describe , short"
4 "summarize"
end

Then some variation of

local N = c(N)
forvalues n = 1/`N' {
    local cmd = command[`n']
    local dta = "a" + strofreal(i[`n'])
    `cmd'
    display "saving to `dta'"
}

produces

. local N = c(N)

. forvalues n = 1/`N' {
  2.         local cmd = command[`n']
  3.         local dta = "a" + strofreal(i[`n'])
  4.         `cmd'
  5.         display "saving to `dta'"
  6. }

Contains data
  obs:             2                          
 vars:             2                          
 size:           496                          
Sorted by: 
     Note: Dataset has changed since last saved.
saving to a2

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
           i |          2           3    1.414214          2          4
     command |          0
saving to a4

Best
Daniel

[Hans Iger]

This works! I did not know that I could store an entire command in a local macro. Have been using this to store values all the time. Sorry for complicating this.
Best regards