Randomly rounding up or down to nearest 10

[Noah Spencer]

I need to randomly round a column of numbers up or down to the nearest 10. Is there a way to do this in Stata?

For example, in the dataset

sysuse auto, clear

, suppose I want to randomly round prices either up or down to the nearest 10. So then the price of the AMC Concord would be either 4,100 or 4,090 with equal probability.

Is there any way I could do this? I was thinking to round everything down and then generate a column of random 10s and zeroes and then adding them, but I'm not sure how to round down.

Thanks!

[Noah Spencer]

Never mind, I think I got it. For the curious:

sysuse auto, clear
gen round_down = 10*floor(price/10)
set seed 123
gen random_var = runiform()
replace random_var = 10 if random_var >= .5
replace random_var = 0 if random_var < .5
gen random_round = round_down+random_var

[Leonardo Guizzetti]

round(x,10)

where x is your variable of interest.

[Nick Cox]

set seed 31459265

gen wanted = cond(runiform() < 0.5, 10 * floor(x/10), 10 * ceil(x/10))

[Elena Draghici]

I have a similar question. I have several excel files where I need to random round the counts (whole numbers, but I would appreciate help with code for decimals too). I want Stata to go into the folder, random round the entire table to base 5 and then write a new document with the rounded counts. Is that possible? The excel files have row names in the first cell and column names (names of variables).

So far I tried:

global path "C:\path\to\directory"
 
foreach file in *{
  import excel using "$path\`file'", sheet("Sheet1") firstrow clear
  tempname temp
  preserve
  temp = round((runiform() - 0.5) / 100, 0.05)
  generate round = round(temp, 0.05)
  replace round = round * 100
  round, dec(0)
  export excel using "$path\`file'", replace
  restore
}

I get an error saying command temp is unrecognized. If I remove it, I get an error saying command round is unrecognized. Could anyone please help me? Even if overwrites the existing excel file, I can work around that.

I would be interested in variations of the code for decimals and a mix of integers and with decimals, and if it could round sheets instead of stand alone documents.

[Nick Cox]

Better to back up and give us an example of a worksheet as imported to Stata and as you want it to be.

When fixed for numerous problems, this code would at best generate random garbage as a new variable.. That can't be what you want.

I started listing problems with your code but the post got too long and I couldn't be sure of completeness.

Someone else may have a different answer, but it is probably better to let others suggest code for you. given a concrete example.

[Elena Draghici]

I have a dataset from which I generate tables. For example, I use longitudinal data to examine educational profiles of individuals across three jurisdictions and years:

use "C:\Users\Elena\LFS Atlantic 2017-2019.dta"
table (year prov) (educ)
collect title educ by year
collect export "C:\Users\Elena\Desktop\test\educ", as(xlsx) sheet(1) cell(A1) modify

the excel table looks like this (on the left is the Stata generated table):

educ by year	STATA									EXCEL ROUNDED
	Highest educational attainment
	0 to 8 years	Some high school	High school graduate	Some postsecondary	Postsecondary certificate or diploma	Bachelor's degree	Above bachelor's degree	Total		0 to 8 years	Some high school	High school graduate	Some postsecondary	Postsecondary certificate or diploma	Bachelor's degree	Above bachelor's degree	Total
Survey year
2017
Province
Newfoundland and Labrador	3,712	7,058	8,595	2,267	17,565	3,770	2,267	45,234		3710	7060	8595	2265	17565	3770	2265	45235
Prince Edward Island	1,700	4,509	6,282	1,804	11,119	4,801	2,119	32,334		1700	4510	6280	1805	11120	4800	2120	32335
Nova Scotia	2,829	9,167	12,044	4,080	22,728	8,430	5,450	64,728		2830	9165	12045	4080	22730	8430	5450	64730
New Brunswick	4,909	8,241	14,155	3,461	20,982	7,517	3,191	62,456		4910	8240	14155	3460	20980	7515	3190	62455
Total	13,150	28,975	41,076	11,612	72,394	24,518	13,027	204,752		13150	28975	41075	11610	72395	24520	13025	204750
2018
Province
Newfoundland and Labrador	3,637	6,702	8,598	1,849	17,682	4,196	2,241	44,905		3635	6700	8600	1850	17680	4195	2240	44905
Prince Edward Island	1,703	4,715	6,511	1,907	11,391	4,852	2,238	33,317		1705	4715	6510	1905	11390	4850	2240	33315
Nova Scotia	2,858	9,250	11,594	3,901	23,001	8,496	5,692	64,792		2860	9250	11595	3900	23000	8495	5690	64790
New Brunswick	4,622	8,167	13,923	3,320	21,345	7,607	3,124	62,108		4620	8165	13925	3320	21345	7605	3125	62110
Total	12,820	28,834	40,626	10,977	73,419	25,151	13,295	205,122		12820	28835	40625	10975	73420	25150	13295	205120
2019
Province
Newfoundland and Labrador	3,277	6,651	8,798	1,658	18,008	4,383	2,255	45,030		3275	6650	8800	1660	18010	4385	2255	45030
Prince Edward Island	1,574	4,498	6,819	1,625	10,926	4,858	2,103	32,403		1575	4500	6820	1625	10925	4860	2105	32405
Nova Scotia	2,638	8,221	11,782	3,617	21,874	8,327	5,765	62,224		2640	8220	11780	3615	21875	8325	5765	62225
New Brunswick	4,728	8,150	14,003	3,010	20,589	7,645	3,176	61,301		4730	8150	14005	3010	20590	7645	3175	61300
Total	12,217	27,520	41,402	9,910	71,397	25,213	13,299	200,958		12215	27520	41400	9910	71395	25215	13300	200960
Total
Province
Newfoundland and Labrador	10,626	20,411	25,991	5,774	53,255	12,349	6,763	135,169		10625	20410	25990	5775	53255	12350	6765	135170
Prince Edward Island	4,977	13,722	19,612	5,336	33,436	14,511	6,460	98,054		4975	13720	19610	5335	33435	14510	6460	98055
Nova Scotia	8,325	26,638	35,420	11,598	67,603	25,253	16,907	191,744		8325	26640	35420	11600	67605	25255	16905	191745
New Brunswick	14,259	24,558	42,081	9,791	62,916	22,769	9,491	185,865		14260	24560	42080	9790	62915	22770	9490	185865
Total	38,187	85,329	123,104	32,499	217,210	74,882	39,621	610,832		38185	85330	123105	32500	217210	74880	39620	610830

I need the table on the left to be randomly rounded to base 5. For demonstration purposes, I rounded to base 5 in excel, but it is not random rounded. I require the rounding to be randomly rounded either up or down. Excel does not have random rounding, so I require a program to do. I have used a SAS Macro in the past, but do not have access to it now.
My goal is to take the Stata generated table and get a new table as (preferably in a new document) that contains these randomly rounded counts. I am not sure if Stata can do this. Another layer to this is that I also have tables that contain numbers with decimals.

[Nick Cox]

It seems to me that you want to round results in tables, not original data, which is what I gathered from #5. That is more difficult than rounding data in variables, and sorry, but I have no suggestions on how to do it. Perhaps there is a way to do it associated with collect, or perhaps that is wishful thinking.

The options seem to include (1) you save your results to a Stata dataset and then round; (2) if you're exporting to MS Excel so that the table is what Excel sees as data, you might find it easier to round in Excel. Excel certainly has random number functions, so I am confident that what you want is programmable in Excel, but I am not a person to suggest code.