Re: st: Need to read a value in a stata table into a variable

[Nick Cox <njcoxstata@gmail.com>]

Thanks for the detail.

Your problem sounds (eventually) like a problem for -collapse-.

collapse (mean) mean_price=price (sd) sd_price=price, by(date vehicle_make_id)

but it sounds as if first you need to -merge- your datasets.

Nothing in your problem description suggests to me that you need to
write your own program. On the contrary, the best thing you can do is
look through the data management manual [D] to see what's available.
Sorry, but the code you show us conveys several major
misunderstandings. Fortunately, that should not bite you because your
problem sounds like fairly standard data management.

Nick

On Mon, Jan 28, 2013 at 9:57 PM, Subramanian, Hemang
<Hemang.Subramanian@scheller.gatech.edu> wrote:
> To restate the question in one line -
>    "read each variable value in an observation into a separate local variable". This is necessary for looping across two conditions.
>
>  Here's exactly what I need to accomplish in stata:
>     I have two datasets - (1) called the auction_date and (2) called the allauctions datasets.
>
>  The auction_date dataset has the following format: (This has about 40000 rows)
>  ID auction_code auction_date
>  ID is the unique identifier and is a number.
>
>  The second dataset is the allauctions datasets which has the following format . This has about 42 million rows.
>  auction_code vehicle_price auction_date vehicle_make_id
>
> The problem statement is as follows:
>   For each auction_date in the auction_date dataset, I need to find the mean standard deviation of all vehicle_prices during the month of auctions across all auctions (from the allauctions dataset), matched by vehicle_make_id
>
>
> The code which failed was as follows:
>
>    foreach var in auction_code sale_date_orig id {
>                 local var`i' = `var'
>                 display var`i'
>                 `i' = `i' + 1
>         }
>
>  I also tried various combinations of foreach &  forvalues, attempting to read the values into the same.
>
> My algorithm(psuedo-code) to solve this problem is as follows: - This still needs work though...
> file open resultfile using "resultPriceVar.txt", write append
> file write resultfile "auction_code" _tab  "sale_date_orig" _tab "mean standarddev"
> For each id in auc_sale {
> local sdo = sale_date_orig
> local ac = auction_code
> load  allauctions
> keep  if (sale_date_orig >= sdo & sale_date_orig <= sdo + 30) [ This is why  I need the exact value of the variable stored in another local variable  ]
>
> by vehicle_make_id auction_center: gen msppac = mean(vehicle_sales_price)
> by auction_center vehicle_make_id: gen dup = _n
> drop if dup > 1
> by vehicle_make_id: egen std_dev = std(msppac)
> by auction_center: egen Price_variance = mean(std_dev)
> file write resultfile auction_code _tab sale_date_orig _tab Price_variance
> }

 From: owner-statalist@hsphsun2.harvard.edu
[owner-statalist@hsphsun2.harvard.edu] on behalf of Nick Cox
[njcoxstata@gmail.com]

> This kind of posting will not evoke precise, useful replies.
>
> 1. When you say that you have a table, it appears that you are
> referring to a dataset. When you talk about rows, you mean
> observations. When you talk about local variables, you mean local
> macros. I encourage you to use Stata terminology on Statalist.
>
> 2. You say that code you represent by multiple dots does not work and
> elicits only the first observation. I can make a few wild guesses at
> what you are doing, but this is not a puzzle list. Please show us the
> exact code that is failing in your eyes.
>
> 3. You assert that you need to loop over observations "to perform
> further analytics". We can have no idea what you mean precisely, but
> that's likely to be quite wrong. The vast majority of Stata problems
> do not involve explicit loops over observations.
>
> My best advice is that you describe the problem you want to solve.
>
> Nick
>
>
> On Mon, Jan 28, 2013 at 9:02 PM, Subramanian, Hemang
> <Hemang.Subramanian@scheller.gatech.edu> wrote:
>
>>   I have a stata table in the following format:
>>  <ac_code> <sale_date> id
>>   1234    1Jan2003    1
>>   1235    2Jan2005     2
>>
>>  Id is the unique identifier for each row. Is it possible to read the  values of ac_code and sale_date into a local variable say "code" and "sd".
>>
>>  I tried using foreach var in ac_code sale_date{...
>> ....
>> ...
>> }
>>  each time  this logic returns only the first row.. in the table.
>>
>>      I need to loop through the entire stata table of approximately 40000 rows, then  use the values stored in local variables "code" & "sd"  to perform further analytics on another larger .dta file (stata file).
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/