Changed date format after changing other values in csv files

[Jaeyong Yoo]

Hello,

I have a csv file with a date variable and other variables from Google Trends.

When I import this file to Stata, I have this preview.

The date column in the preview looks different from the original CSV file. (1/1/2004 vs. 2004-01-01)

The problem is that if I changed the value in the CSV file, the format of the date column also changed.

I changed 17 to 12 in the hits column (first row) and saved it as CSV.

You can see the format of the date column changed.

Could you help me keep the same format (yyyy-mm-dd) after changing values?

I attached the CSV file.

Thanks!

[Jared Greathouse]

What you're asking for is likely possible, but I don't understand why you'd want to do this. You can make a date variable in Stata format anyways, why does the order matter in this instance?

[Jared Greathouse]

To prove this, we can just do

import delim "https://www.statalist.org/forums/filedata/fetch?id=1642851", clear


g date2 = date(date,"YDM")

format date2 %td

keep hits keyword geo category date2 gprop

rename date2 date

br

with this in mind, why does it matter what format the date variable is in?

[Jaeyong Yoo]

Thanks for your help, Jared.

Since I have several CSV files (more than 50) in the folder and want to import them into Stata, using "foreach". If I have the same format of date, I can easily create year and month columns from the date column. If there are two different formats, I don't know how to create year and month columns from two different formats in the same column.


For example, I want to create year and month column from this original dataset.

Date	hit
1/1/2004	1
1/2/2004	5
1/3/2004	10
2004-1-4	7
2004-1-5	8

I want this.

Date	hit	year	month
1/1/2004	1	2004	1
1/2/2004	5	2004	1
1/3/2004	10	2004	1
2004-1-4	7	2004	1
2004-1-5	8	2004	1

If they all look like mm/dd/yyyy, I can use this command: gen year = real(sybstr(date, -4, 4)). But, I cannot use this command because there are two formats in the column.

[Leonardo Guizzetti]

As long as each csv file is imported such that dates are in the Stata internal format for dates, then you can combine each dataset and later extract or reformat as you see fit.

[Jared Greathouse]

Oooooooooooooh. That might be a bit of a problem then.


My advice is the find the ones that have the same format (hopefully most are formatted the same) and then loop over these and append them.

[Jaeyong Yoo]

As long as each csv file is imported such that dates are in the Stata internal format for dates, then you can combine each dataset and later extract or reformat as you see fit.

I use "foreach" to import altogether.

Like you said, I might combine all 50 CSV files into one CSV file and then import it. But, I hope I can use more convenient way to do it.

I have been thinking that I only find and select rows including "/" and change values like another format.