Question on building a dataset in Stata using loops

[Christopher Ujma]

Hi!

I have 10 separate csv datasets with transaction level data on all exports recorded in South Africa from 2020 - 2021, with each separate file containing the exports to a different country. Hence, an individual observation/row of data in this file is a single recorded export that looks like this:

TradeType

DistrictOfficeCode

DistrictOfficeName

CountryOfOrigin

CountryOfOriginName

CountryOfDestination

CountryOfDestinationName

Tariff

StatisticalUnit

TransportCode

TransportCodeDescription

YearMonth

CalendarYear

Section

SectionAndDescription

Chapter

ChapterAndDescription

TariffAndDescription

StatisticalQuantity

CustomsValue

WorldRegion

Exports

GMR

Germiston/Alberton

ZA

South Africa

CN

China

22082019

LI

1

Maritime

202106

2021

4

4 - Prepared foodstuffs

22

22 - Beverages, spirits and vinegar

22082019 - Other

90

7951

ASIA

I am trying to write a loop which essentially loads each csv file one at a time, transforms it (collapses the data to the port/district office it originates from), and then saves the transformed data into a file on disk. However, I do not know how to code the loop so that it "stacks" each iteration of the transformed data into the same file. What I have so far is:


forval i= 1/10 {

clear
capture import delimited using "C:\Users\Christopher Ujma\Documents\\Report (`i').csv", case(preserve)
//generate variable to count number of shipments
gen NumberOfShipments = 1
//Collapse data to Origin, Destination and Time
collapse (sum) NumberOfShipments CustomsValue, by(DistrictOfficeCode CountryOfDestinationName YearMonth)

//command to save to file on disk
}

Essentially I am looking to use something similar to the append command, but instead of appending data from a file on disk to data in memory I am looking to do it the other way around. Also, just a note that I am using Stata 15.

Thank you!!

[Daniel Feenberg]

-file write- has an append option, but it doesn't do .dta files, only text or binary files. I think the most practical thing for you to do is to write 10 separate .dta files

save foo`i'

and then write a loop to append them together. Note that -append- can append to an empty workspace, no there is no need to treat the first dataset specially.

clear
forval i=1/10 {
   append foo`i'
}

[Andrew Musau]

My guess is that you want:

tempfile holding stack
forval i= 1/10 {
    clear
    capture import delimited using "C:\Users\Christopher Ujma\Documents\\Report (`i').csv", case(preserve)
    //generate variable to count number of shipments
    gen NumberOfShipments = 1
    //Collapse data to Origin, Destination and Time
    collapse (sum) NumberOfShipments CustomsValue, by(DistrictOfficeCode CountryOfDestinationName YearMonth)

    //command to save to file on disk
    save `holding', replace
    if `i'==1{
        save `stack', replace
    }
    if `i'>=2{
        use `stack', clear
        append using `holding'
        save `stack', replace
    }
}
use `stack', clear

In any case, review FAQ Advice #12 on how to use the dataex command to provide data examples in case this suggestion or #2 does not solve your problem.