Reshape with Indicators

[Christian Beer]

Hello Statalisters!

I have a data formatting issue that leaves me sleepless for nights. I know it must be simple to solve but I simply have no idea.

In the dataset below (excerpt) I have 7 different "sanction types" imposed by countries/associations on various dates. Now, as I have a column for the sanction type, it is unnecessary to have 7 more columns with the sanction type that provides a "1" whenever that sanction type is imposed by the country on that date. I want to drop the column "sanction type" and collapse the rows, so that I have date-country-specific data, where in each row the sanction type is marked by a "1" in the respective column. In the same row!

I have following data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date str14 country str10 sanctiontype byte(financial travel individual export import tradesupport nosanction)
"2022-02-21" "United States"  "financial"  1 . . . .
"2022-02-21" "United States"  "individual" . . . . . . .
"2022-02-21" "United States"  "import"     . . . . 1 . .
"2022-02-21" "United States"  "export"     . . . . . . .
"2022-02-21" "European Union" "financial"  1  . . . . .
"2022-02-21" "European Union" "individual" . . 1 . . . .
"2022-02-21" "European Union" "travel"     . . . . . . .
"2022-02-22" "United States"  "financial"  1 . . . . . .
"2022-02-22" "United States"  "individual" . . 1 . . . .
"2022-02-22" "United Kingdom" "financial"  1 . . . . . .
"2022-02-22" "United Kingdom" "individual" . . 1 . . . .
"2022-02-22" "United Kingdom" "travel"     . 1 . . . . .
"2022-02-22" "Germany"        "import"     . . . . 1 . .
"2022-02-23" "European Union" "financial"  1 . . . . . .
"2022-02-23" "European Union" "individual" . . 1 . . . .
"2022-02-23" "European Union" "import"     . . . . 1 . .
"2022-02-23" "European Union" "export"     . . . 1 . . .
end

and I wish to convert to:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date str14 country byte(financial travel individual export import tradesupport nosanction)
"2022-02-21" "United States"  1 . 1 1 . . .
"2022-02-21" "European Union" 1 1 1 . . . .
end

-----------


I hope you see my point and I appreciate any help! Thank you!

Best regards Chris

[Clyde Schechter]

I'm completely confused by #1. I do not understand how you got from the example data as input to what you show you want to get to. Let's look, for example, at 2022-02-21 and United States. There are three such observations in the example. The second one has a 1 on import, and the other two have only missing values. Yet somehow in your desired result you have 1 for financial, individual, and export! Where did those come from? And why did the 1 for import disappear? What's going on? Similarly for 2022-02-21 and European Union, I do not understand how those desired results relate to the original data.

[Ken Chui]

Agree with #2's observation. Assuming it's just sloppy transcription in #1, conceptually this can be done with collapse:

drop sanctiontype
collapse (sum) financial-nosanction, by(date country)

Also., there are multiple typos in the first data set. E.g. lines 3 and 5 both are missing their corresponding "1" in the data. It may be worth to clean them up using code to create them. Here is one example:

tab sanctiontype, gen(type)