looping through rows

[Meerim Asanbekova]

Dear Stata Users,

I hope this email finds you well. I'm reaching out seeking assistance with a data retrieval task.

I have a dataset structured as follows:

Code	Region	Name	Latitude	Longitude
GB	Europe	London	51.5074	-0.1278
US	North America	New York	40.7128	-74.006
JP	Asia	Tokyo	35.6895	139.6917
FR	Europe	Paris	48.8566	2.3522
AE	Middle East	Dubai	25.2769	55.2962
SG	Asia	Singapore	1.3521	103.8198
DE	Europe	Berlin	52.52	13.405

I aim to download climate data by leveraging the latitude and longitude coordinates of each city. Specifically, I need to access the following API link, iterating/looping through each city:
For instance, for the initial iteration (for London), the link to be utilized is: https://power.larc.nasa.gov/api/temp...=1981&end=2022


Here's the syntax I attempted to use:

clear
local      code      "GB"      "US"      "JP"       "FR"      "AE"      "SG"      "DE"
local      region   "Europe"            "North America"             "Asia"   "Europe"              "Middle East"    "Asia"   "Europe"
local      city        "London"           "New York"        "Tokyo" "Paris"  "Dubai"              "Singapore"       "Berlin"
local      lat          "51.5074"           "40.7128"           "35.6895"           "48.8566"              "25.2769"           "1.3521"             "52.52"
local      long       "-0.1278"            "-74.006"            "139.6917"         "2.3522"              "55.2962"           "103.8198"         "13.405"
 
foreach region in `region' {
foreach code in `code'{
foreach name in `name'{
foreach long in `long'{
              foreach lat in`lat'{
 
local opt= cond(`name'=="London", "replace", "append")
import delimited"https://power.larc.nasa.gov/api/temporal/monthly/point?parameters=T2M,PRECTOTCORR_SUM,PRECTOTCORR&community=AG&longitude=`long'&latitude=`lat'&format=CSV&start=1981&end=2022", rowrange(12) varnames(12)
gen name=`name'
gen code=`code'
gen region=`region'
 
append using "C:\Users\User\Desktop\precdata.dta" if `opt'=="append"
save "C:\Users\User\Desktop\precdata.dta", replace
clear
local i=`i'+1
di `i'
}
}
}
}
}

Unfortunately, I encountered challenges in obtaining the table like this:

ISO Code	Region	City	Latitude	Longitude	PARAMETER	YEAR	JAN	FEB	…	NOV	DEC	ANN
GB	Europe	London	51.5074	-0.1278	T2M	1981	3.53	2.28	…	6.58	0.19	9.15
GB	Europe	London	51.5074	-0.1278	T2M	1982	2.71	4.19	…	7.75	3.48	9.87
GB	Europe	London	51.5074	-0.1278	T2M	1983	5.73	1.24	…	6.61	4.83	9.77
…	…	…	…	…	…	…	…	…	…	…	…	…
GB	Europe	London	51.5074	-0.1278	T2M	2020	5.95	6.36	…	8.55	5.02	11
GB	Europe	London	51.5074	-0.1278	T2M	2021	2.81	4.6	…	6.62	6.4	10.21
GB	Europe	London	51.5074	-0.1278	T2M	2022	3.91	6.58	…	9.3	4.07	11.56
US	North America	New York	40.7128	-74.006	T2M	1981	3.53	2.28	…	6.58	0.19	9.15
US	North America	New York	40.7128	-74.006	T2M	1982	2.71	4.19	…	7.75	3.48	9.87
US	North America	New York	40.7128	-74.006	T2M	1983	5.73	1.24	…	6.61	4.83	9.77
…	…	…	…	…	…	…	…	…	…	…	…	…
US	North America	New York	40.7128	-74.006	T2M	2020	5.95	6.36	…	8.55	5.02	11
US	North America	New York	40.7128	-74.006	T2M	2021	2.81	4.6	…	6.62	6.4	10.21
US	North America	New York	40.7128	-74.006	T2M	2022	3.91	6.58	…	9.3	4.07	11.56

Any guidance or assistance you could provide would be greatly appreciated.
Thank you in advance for your support.

Warm regards,
Meerim

[Ken Chui]

Welcome to Statalist.

This may be better as a program and not a set of nested foreach loops. You're using a compound loop and it will loop through all possible combinations, ultimately leading to odd combination like JP in North America city Berlin.

Look into the help of program (help program). And if you need more concrete advice, read the FAQ and pay attention to section 12 regarding asking a easy-to-answer question. And please follow those instructions. The current question does not contain enough specific details, that includes the error encountered ("I encountered challenges in obtaining the table like this" does not really convey what went wrong.)

[alejoforero]

I agree with the previous answer that your question is not very descriptive of the type of challenges you encountered.

Nevertheless, there is a clear problem in your loops

 
 foreach region in `region' {

is problematic, because what a foreach loop

foreach lname in any_list {

does is to create a local lname that iterates through the values of any_list.

lname and any_list cannot have the same name, otherwise it will replace itself and never do anything.

[Meerim Asanbekova]

Dear Ken Chui and alejoforero,

Thank you both for taking the time to review my query and providing valuable feedback. I appreciate your insights, and I apologize for the lack of clarity in my initial post.

Ken, your points about the potential issues with nested foreach loops and the importance of clarity in framing the question were well-taken. I understand the potential pitfalls associated with my initial approach and recognize the need for a more robust solution.

Alejo, I'm grateful for your observation regarding the variable naming conflict. It's clear that such conflicts could lead to unexpected behavior in the code, and I'll ensure to avoid such naming clashes in the future.

As I'm new to Stata, I've been struggling to create a workable syntax despite reading related manuals and posts. I'm unsure about the best approach to rectify this issue and would greatly appreciate any guidance or suggestions you could offer in this regard.

I'm specifically looking for guidance on how to rewrite the code using loops instead of a program structure. Creating programs in Stata is challenging for me, so I'd appreciate any tips or advice you can provide in this area. Additionally, I'd like assistance in ensuring that the climate data is correctly appended to the dataset, as intended.

It's likely that I need to rewrite my post for clarity, and I'm open to any suggestions you may have.

Once again, thank you both for your assistance and expertise. Your guidance has been invaluable, and I'm eager to incorporate your suggestions into my workflow.

Warm regards,
Meerim

[Ken Chui]

See this tutorial: https://libguides.library.nd.edu/dat...stata/programs

And assuming that API call works in #1, a draft program may look something like this:

capture program drop WhateverName
program define WhateverName
clear
import delimited"https://power.larc.nasa.gov/api/temporal/monthly/point?parameters=T2M,PRECTOTCORR_SUM,PRECTOTCORR&community=AG&longitude=`5'&latitude=`4'&format=CSV&start=1981&end=2022", rowrange(12) varnames(12)
generate city="`3'"
generate code="`1'"
generate region="`2'"
save data_`1', replace
end

* 1: code; 2: region; 3: city, 4: lat, 5: long
WhateverName "GB" "Europe" "London" 51.5074 -0.1278
WhateverName "US" "North America" "New York" 40.7128 -74.006
* so on, so forth...

This should get you started.

[alejoforero]

I think you are on a good path and could make it work with either loops or programs.

What I think you are missing is a debugging strategy. I myself wouldn't be able to make a 4 level nested loop work in a single run, much less with an API call. I would suggest you work from the ground up, adding one level at a time.
You already know about the display command in Stata, equivalent to print("") in other languages. That is the key.

Just try something like this as a debugging tool for the loop:

 
 foreach region in regionA regionB {      display "Region is `region'" }

Debugging API calls is also a common problem that requires some tinkering. There are several tools designed to do that, like Postman. Check this StackOverflow post.
https://stackoverflow.blog/2022/02/2...api-consumers/

Best luck.