Foreach Loop Across All Variables of A Certain Format

[Priscilla Fung]

Hi,

I am trying to export a dta file (about 10,000 variables of different formats) as a CSV file so that I can view it as an excel spreadsheet. However, some of my variables are dates with commas (format %dM_d,_CY)

Is there a way to create a foreach loop across all variables of the database, and format them to %tdNN/DD/CCYY only if they either are in %dM_d,_CY format or have values containing commas?

The code below currently produces an error code stating, "varlist must contain all numeric or all string variables," labelled as, "r(109)." Can the code below be corrected, and/or is there an alternative solution?

foreach var of varlist _all {
format %tdDD-NN-CCYY if ustrpos(name, ",")>0 | strpos(name, ",")>0
}

Thank you in advance!
Priscilla

[Nick Cox]

This may help. Nothing has been tested.

ds, has(format %dM_d,_CY) 
local vars1 `r(varlist)' 
ds, has(format *,*) 
local vars2 `r(varlist)' 
local vars : list vars1 | vars2 
format `vars'  %tdNN/DD/CCYY

With findname (Stata Journal) you could go

findname, format(%dM_d,_CY) local(vars1) 
findname, format(*,*) local(vars2) 
local vars : list vars1 | vars2 
format `vars'  %tdNN/DD/CCYY

[Clyde Schechter]

You do not provide example data, so I'm driven to speculate a bit about what's going on. But it seems like you have some actual Stata date variables that you want to change to display format %tdDD-NN-CCYY, and then you have some other string variables that read as dates to human eyes, and you would like to make them into real Stata date variables and display them with format %tdDD-NN-CCYY.

Those two cases require very different treatment, and I don't see any good way to do them all together. I would handle them separately:

ds, has(format %dM_d,_CY)
format `r(varlist)' %tdDD-NN-CCYY

ds, has(type string)
foreach v of varlist `r(varlist)' {
    gen _`v' = daily(`v', "MDY"), after(`v')
    capture assert missing(`v') == missing(_`v')
    if c(rc) == 0 {
        format `v' %tdDD-NN-CCYY
        drop `v'
        rename _`v' `v'
    }
    else { // DOES NOT ACTUALLY CONTAIN DATES
        drop _`v'
    }
}

Because there was no example data, this code may not work in your data set. Moreover, without example data, it is untested and may contain typos or other problems. If this code does not work properly, please show example data when posting back, and be sure to use the -dataex- command to do so. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Priscilla Fung]

Thank you so much for your responses Nick and Clyde!

Yes, I am trying to convert Stata dates already in the format %dM_d,_CY to %tdDD-NN-CCYY. I manually corrected the formats for all string variables reading as dates previously.

I ran the code below and confirm that it works!
ds, has(format %dM_d,_CY)
format `r(varlist)' %tdDD-NN-CCYY

I will consider including example data with future questions!

Thanks again!
Priscilla