Export output of levelsof

[Pim Mante]

Hi Statalists!

This will be my first posts, but I have been lurking around this forum for a while looking at answers provided to questions similar to ones I have had about Stata.
Currently I am working with a dataset with a large number of stock trading observations. After having cleaned up part of the data I now wish to extract all the unique cusip id's (= a string variable). Using: levelsof cusip, clean Stata shows the unique values I wish to export. However when trying to get Stata to export this into an excel file via putexcel (A1) = (levelsof cusip, clean) is gives an error saying levelsof not found. I suspect that this means that it it not possible to export the results of levelsof via this method but am not sure as I am still quite new to Stata. I hope someone here is able to help me export the unique id's and enlarge my understanding of Stata.

Kind regards,

Pim

[FernandoRios]

Hi Pim
Try the following:

levelsof cusip, matrow(cusid)
putexcel (a1)=cusid

This way levelsof stores unique variables in a matrix that can then be exported to excel.
HTH
Fernando

[Nick Cox]

Fernando Rios gives excellent advice, but for "unique variables" read "distinct values".

[Pim Mante]

Hi FernandoRios,
Thank you for your swift reply, and Nick for your comment. Unfortunately using matrow with cusip is not an option as my cusip variable is a string variable. Converting it without loss of data is not possible as it contains nonnumeric data. In order to later match the cusip values with option prices I need to have the exact match. I do not need to export it to excel perse. I can also just store it as a text file with all the distinct value's of cusip on seperate rows. I know there must be a way to do so instead of copying it manually from the output Stata gives in the result screen.

[Andrew Musau]

frame put cusip, into(Excel)
frame Excel: bys cusip: keep if _n==1
frame Excel: export excel cusip using myfile
frame drop Excel

[Pim Mante]

Hi Andrew Musau ,

Thank you for your suggestion. Unfortunatly I have Stata 15.1 and from what I've read the frame command only works in Stata 16. I've created a separate file with only the cusip values after filtering and sorted them. Now I need to figure out a way to only keep them if it is the first time they are mentioned. (This is what I assume your suggested code does). Whenever I try to do so with the keep if _n==1 it only keeps the first value however, not the first time a cusip observation appears in the file.

[Nick Cox]

Please see https://www.statalist.org/forums/help#version on declaring versions before the latest.

preserve
bysort cusip : keep if _n == 1
export excel cusip using myfile
restore

should work in 15.

It's not clear why you care when a code was first mentioned but this preserves that information in terms of observation numbers. If you want to use a time variable instead, then fine.

preserve
gen long obsno = _n 
bysort cusip (obsno) : keep if _n == 1
export excel cusip obsno using myfile
restore

[Pim Mante]

Hi Nick Cox

Thank you for your reply. I'll be sure to mention the Stata version I'm using next time I start a discussion. Thank you for the code you mentioned, it indeed provided me with the output I wanted. Sorry for my vague wording, I do not care when a code is first mentioned. I assumed this is what Stata looked at after sorting the values, but I am still new to reading Stata code, and it seems I was wrong there. Using help bysort has given me more insight in the functionality of the code.

Thanks again for the suggestion everybody.

[Nick Cox]

No;

sort cusip

might be satisfied by many observation orders. If I feed to sort

A B A B A B B A

then it's manifest that

A A A A B B B B

should be what the result looks like, but Stata is by default indifferent to where the As and Bs came from. That is why there is a stable option to insist that original order is respected.