looping local macro

[Nazzarena]

Hi

I was hoping to import efficiently some data with a little local programming but I am employing more time in devising a way to do so than I would have used copying and pasting, so I presume to ask for help.
Suppose I have a file with viral load for each of a number of individuals, named after the individual, with suffix _VL.tab (exported from a database by hand). The ID for such individuals are in another file, "missing longitudinal.csv". The data collected are already in the file "VL.dta", which contains the variables ID VL date. Everything is in the current directory.
The loop runs but does not produce anything. Nor does it even return an error. The correct values are shown upon creation of the local but display does not show its content.
What am I doing wrong? Thanks much,

NL


insheet using "missing longitudinal.csv", clear names
drop if ID==""
levelsof ID, local(individuals)

foreach ID of local individuals{
insheet using `ID'_VL.tab, names clear
keep v2 v3
rename v2 date
rename v3 VL
gen ID=ID
append using VL
save VL, replace
}

[Jeph Herrin]

What do you mean, "display does not show its content"? If your code is not producing an error, nor anything else, I would presume that the local -individuals- does not contain any values, so the loop is never entered. Can you add

. di "`individuals'"

after -levelsof-? So we can see what, if anything, is stored there.

[Nazzarena]

yes, using di `individuals' it appears nothing is stored... however after levelof all the correct values are shown...
also, the macro is not listed in macro dir...
however, if I do di r(levels), i obtain the correct list...

[William Lisowski]

Are you running your commands from a do-file? And if so, are you perhaps running them by selecting a few lines at a time, so that you first run the levelsof command and then run the foreach loop? In that case, the local macro individuals disappears when the (temporary) do file within which it was run concludes. In other words, running a few lines at a time from within a do file is not the same as running a few lines at a time from the command window.

[Nazzarena]

Thanks! It was so. However it was not the only problem... now that the macro is there, foreach iterates only once and than returns ID not found. (not `ID' not found)
IDs are alphanumeric of variable length and I had to remove "clean" option in levelsof and the macro shows a single large string. If I do keep "clean" it says that the first ID is invalid...

[Nick Cox]

You probably intend

 
gen ID = "`ID'" 

ID on the RHS would need to be an existing variable or scalar. But you are trying to create that variable, using the macro contents.

[Nazzarena]

yes, of course, thank you as always. Sorry I am transcribing the code with generic intelligible names and introduced a mistake. Foreach still doesn't work... I think the issue is it can't read past the first value in the local. In fact the local contains one long string, how does stata know to parse values? However I tried the option sep with no change whatsoever

[Nazzarena]

The error however appears to be even earlier in the append line. Now, with the correction foreach run trough one iteration and then it returns "file specification invalid"

[William Lisowski]

Your append command is telling Stata to append the contents of the dataset VL to the data in memory. The first time through the loop, the file VL does not yet exist.

I recommend adding the following commands just before the loop to create an empty VL file.

clear
save VL, replace emptyok

[Nazzarena]

The file pre-exist....Only the data from the first ID is appended to it, however.
(In explanation: I first imported a collection of old data that our kindly postdoc made, and created ID.dta, to which I want to append some more 25 sets of data which I had to download manually from a more recent DB)

[William Lisowski]

You didn't happen to have in your do-file

append using `VL'

rather than

append using VL

did you? Because there is no VL macro defined. I can recreate the error message from Stata by using the first command above.

By the way, you wrote that your postdoc made a file called ID.dta, surely you meant VL.dta given the code you showed us.

[Nazzarena]

yes, append using VL. Yes, IDs in file: "missing longitudinal.csv". Previously collected data in file "VL.dta", which contains the variables ID VL date. All in current dir.
The files can be correctly appended manually. The foreach does not iterate past the first instance.

[Nazzarena]

the short loop:

foreach ID of local individuals{
insheet using `ID'_VL.tab, names clear
}

returns the same error,
invalid file specification
r(198);

indicating that reading the local macro is likely the problem...

[William Lisowski]

Then the advice at #2 above is again worth taking: place display "`individuals'" into your do-file just before the loop and see what the macro looks like.

Also, at #7 you talk about transcribing code with generic intelligible names to post. This is not a good idea, as you saw by accidentally introducing a mistake in your original post. You should review the Statalist FAQ linked at the top of the page, especially #12 with guidance on copying and pasting your log into a CODE block for posting. It is preferable to present exactly what you have done and what Stata responds, than to try to edit it to make it clearer.

Finally, while you're looking at the FAQ, please note the preference on Statalist for full names.

[Nazzarena]

I have a di which correctly returns the values. Inserting in levelsof the options

clear sep(,)

results in the parsed alphanumeric string (actual values)

18K,378,449Z,572,580,67K,89J,90K,95D163,95I,96D163 ,977Z,A68Z,AD01,AH49,AJ01,AZ15,C17Z,DBN2,VWK,XCC,Z A43,ZG17

Stata doesn't like some values: Foreach loops trough the first and second iteration and stops for invalid file.
However, by hand any of the first 3 values is a permissible filename.