About mspak

mspak · ‎01-27-2013

Hi all, I tried out the solution for my SMTH1 answer but yet to get the answer for SMTH2. My SAS code to calculate SMTH1 is as follows: PROC SORT DATA= final.test2 OUT=TEST NODUPKEY; BY GVKEY FYEAR; RUN; proc expand data=TEST OUT=TEST1 method=none; by GVKEY; convert IB_lat = lag1_IB / transformout = (lag 1); convert IB_lat = lag2_IB / transformout = (lag 2); convert IB_lat = lag3_IB / transformout = (lag 3); convert IB_lat = lag4_IB / transformout = (lag 4); convert cfo_lat = lag1_CFO / transformout = (lag 1); convert cfo_lat = lag2_CFO / transformout = (lag 2); convert cfo_lat = lag3_CFO / transformout = (lag 3); convert cfo_lat = lag4_CFO / transformout = (lag 4); run; DATA TEST2; SET TEST1; SD_IB = STD(IB_lat, lag1_IB, lag2_IB, lag3_IB, lag4_IB); iF MISSING (IB_lat) THEN miss_ib = 1; else miss_ib = 0; iF MISSING (lag1_IB) THEN miss_lag1_IB = 1; else miss_lag1_IB = 0; iF MISSING (lag2_IB) THEN miss_lag2_IB = 1; else miss_lag2_IB = 0; iF MISSING (lag3_IB) THEN miss_lag3_IB = 1; else miss_lag3_IB = 0; iF MISSING (lag4_IB) THEN miss_lag4_IB = 1; else miss_lag4_IB = 0; totalmissIB = SUM(miss_ib, miss_lag1_IB, miss_lag2_IB , miss_lag3_IB, miss_lag4_IB); IF totalmissIB > 2 THEN SD_IB = .; SD_CFO = STD(cfo_lat, lag1_CFO, lag2_CFO, lag3_CFO, lag4_CFO); iF MISSING (CFO_lat) THEN miss_ib = 1; else miss_ib = 0; iF MISSING (lag1_CFO) THEN miss_lag1_CFO = 1; else miss_lag1_CFO = 0; iF MISSING (lag2_CFO) THEN miss_lag2_CFO = 1; else miss_lag2_CFO = 0; iF MISSING (lag3_CFO) THEN miss_lag3_CFO = 1; else miss_lag3_CFO = 0; iF MISSING (lag4_CFO) THEN miss_lag4_CFO = 1; else miss_lag4_CFO = 0; totalmissCFO = SUM(miss_ib, miss_lag1_CFO, miss_lag2_CFO , miss_lag3_CFO, miss_lag4_CFO); IF totalmissCFO > 2 THEN SD_CFO = . ; SMTH1 = -SD_IB/SD_CFO; RUN; Hope this can provide some insight to the solution of smth2. Thank you. Regards, mspak

mspak · ‎01-26-2013

Good days to all, I have a dataset (as attached) with the following variables; gvkey = company identification code fyear = financial year IB_LAT = earnings CFO_LAT = cash flows ACCR_LAT = accruals I wish to calculate the following variables: SMTH1 is the standard deviation of earnings (IB_LAT) divided by the standard deviation of cash flows (CFO_LAT), where standard deviations are calculated using a minimum of three and maximum of five years of data (t-4 to t); SMTH1 is multiplied by negative one so that larger values, i.e., values closer to zero, represent smoother earnings. SMTH2 is defined as the correlation between the cash flows (CFO_LAT) and total accruals (ACCR_LAT), correlations are calculated using a minimum of three and maximum of five years of data (t-4 to t); SMTH2 is multiplied by negative one so that larger values, i.e., values closer to one, represent smoother earnings. I intend to use either DATA step or Proc Expand to calculate the variables. However, I have no idea how to ensure I have at least 3 years of data available to fulfill such definitions. *t-4 to t = 5 years = lag4(x), lag3(x), lag2(x), lag1(x), x. Is the option for "correlation" available in proc expand? I found a blog that is close to my query: Rolling Correlation Window [Archive] - Statistics Help @ Talk Stats Forum By referring to proc expand user guide: SAS/ETS(R) 9.3 User's Guide, the following commands are available, I'm not sure to what extend these codes can be applied to my case: > number Missing value if , else x >= number Missing value if , else x = number Missing value if , else x = number Missing value if , else x < number Missing value if , else x <= number Missing value if , else x Thank you and hope to get any help. Regards, mspak

mspak · ‎01-24-2013

Hi all, I modified the codes as follows to generate my desired output: proc sort data=final.test out=test1 nodupkey; by fyear sic2 au; run; proc sort data=test1 out=test2; by sic2 fyear ; run; proc rank data=test2 descending out=test3; by sic2 fyear ; var nat_mkshr; ranks rnk_nat; run; proc sort data=test3 out=test4; by sic2 fyear descending rnk_nat; run; data test5; set test4; by sic2 fyear descending rnk_nat; natshr_dif = dif(nat_mkshr); IF FIRST.FYEAR THEN DO; natshr_dif = .; END; run; proc sort data=test5 out=test6; by sic2 fyear cbsa; run; proc rank data=test6 descending out=test7; by sic2 fyear cbsa; var City_Mkshr; ranks rnk_city; run; proc sort data=test7 out=test8; by sic2 fyear cbsa descending rnk_city; run; data test9; set test8 (drop=gvkey); by sic2 fyear cbsa descending rnk_city; cityshr_dif = dif(City_Mkshr); IF FIRST.CBSA THEN DO; cityshr_dif = .; END; run; data test10; set test9; if rnk_nat = 1 and natshr_dif>=0.1 then nat_AIP1 = 1; else nat_AIP1 = 0; if rnk_city ^= 1 OR . <cityshr_dif <0.1 then city_AIP1 = 0; else city_AIP1 = 1; run; Thank you. Regads, mspak

mspak · ‎01-24-2013

Hi Jagadish and EJ, There was a typo error on the variable (rnk_city instead of rnk_nat). I checked the code for a few times, but I could not realise the mistake . Thank you helping me . Regards, mspak

mspak · ‎01-24-2013

Hi EJ, Thanks for your helps. I try to run the following code: proc sort data=final.test out=test1 nodupkey; by fyear sic2 au; run; proc sort data=test1 out=test2; by sic2 fyear ; run; proc rank data=test2 descending out=test3; by sic2 fyear ; var nat_mkshr; ranks rnk_nat; run; proc sort data=test3 out=test4; by sic2 fyear descending rnk_nat; run; data test5; set test4; by sic2 fyear descending rnk_nat; natshr_dif = dif(nat_mkshr); run; proc sort data=test5 out=test6; by sic2 fyear cbsa; run; proc rank data=test6 descending out=test7; by sic2 fyear cbsa; var City_Mkshr; ranks rnk_city; run; proc sort data=test7 out=test8; by sic2 fyear cbsa descending rnk_city; run; data test9; set test8; by sic2 fyear cbsa descending rnk_nat; cityshr_dif = dif(City_Mkshr); run; I found everything is within my expectation up to the output of test8. I found the following error whenever I try to output "test9". I found that the obs in test9 has drop significantly compared to as in test8. ERROR: BY variables are not properly sorted on data set WORK.TEST8. Thank you. Regards, mspak Do you know the reason?

mspak · ‎01-24-2013

Good days to all, I have a set of data (as per attached), with the following variables: gvkey = US company identification code fyear = financial year Auditor = auditor identification code sic2 = 2-digit industry classification code CBSA = City NAT_Mkshr = National market share, based on SIC2 and fyear. City_Mkshr = City market share, based on CBSA, SIC2 and fyear. I would like to calculate Auditor Industry Specialist, based on the following definitions: National auditor specialist: An auditor is defined as a national industry specialist if it has the largest annual (ie fyear) market share in an industry, based on the two-digit SIC category, and if its annual market share is at least 10 percentage points greater than its closest competitor in a national audit market. City auditor specialist: An auditor is defined as a city industry specialist if it has the largest annual (ie fyear) market share in an industry, based on the two-digit SIC category for a given city (ie. CBSA), and if its annual market share is at least 10 percentage points greater than its closest competitor in a city audit market. I learned from my search that there are SAS function, namely LARGEST and COMPARE. I have no idea on whether these functions can be applied in my case. Hope to get any simple code to my problem. Thanks in advance for any help. Regards, mspak

mspak · ‎11-18-2012

Hi Ksharp, Thank you for your clarification. I think the observations without a valid zipcodes will not included in the calculation of total_sale. But the final step, which combined the total_sale (total_sale dataset) with the sale (in the original data: Geo_shr) will include the SALE figures of the firms with invalid zipcodes. Any, this is fine - I will not include the observations without a valid zipcode in my analysis (as my study is based on valid zipcode in US). data want; if _n_ eq 1 then do; if 0 then set total_sale; declare hash h(dataset:'total_sale'); h.definekey('fyear','sic3','zip'); h.definedata('total_sale'); h.definedone(); end; set x.Geo_shr; call missing(total_sale); rc=h.find(); percent=divide(sale,total_sale) ; drop rc; run; Thank you for your valuable suggestions. Regards, mspak

mspak · ‎11-18-2012

Hi Ahmed, How to execute it? %getMarketShare(p_inDsName=comm.geo_shr ,p_year=2009, p_SIC3=641, p_radius=250, p_outDsName=work.marketShares); The above function was suggested. What does this mean? Input data=geo_shr, fyear=2009, sic3={all 641 categories of industry} and p_radius={within 250 miles radius}, output data=marketshares. Is this correct? Thank you. mspak

mspak · ‎11-17-2012

Dear all, I think I understand the reason why I get the missing TOTAL_SALE with non-missing SALE figures. This is due to the ZIP uploaded into hash object was included in the zipcitydistance function. If I wish to ensure all the zipcodes (in SAS) are up-to-date, I have to download the latest version of zipcodes database from SAS official web. Thank you for helps, especially for Ksharp and Authur Tabaneck Regards, mspak

mspak · ‎11-17-2012

Dear AhmedAl-Attar, In order to execute the macro suggested, I think I need to repeat the steps for each combination of year and SIC3, Is it correct? Thank you. Regards, mspak

mspak · ‎11-16-2012

Hi, Very much appreciated your helps. In the output file 'want'. Some of the observations with SALE figures but missing TOTAL_SALE figures. This is the reason that I think the TOTAL_SALE does not include SALE for the firm itself. For example, there are 10 other firms located within 60 miles radius from the firm A. Then the TOTAL_SALE should be based on SALE of 11 firms (10 other firms plus the firm A itself). If there is NO any match based on 60 miles radius, same SIC3 and same FYEAR, then TOTAL_SALE = SALE of firm A. Missing TOTAL_SALE is possible only when there is no other firm within 60 miles radius from that firm (ie. firm A) and that firm (ie. Firm A)'s SALE is missing. We should use zip to calculate the distance. Firms in the same zip considered as the same point. But firms in the same zip should be considered as firms within 60 miles radius (ie. zero mile radius) as well. Let say, There are 5 other firms with the same zip, same sic3 and same fyear as per the firm (firm A) and 3 other firms with different zip, same sic3 and same fyear but within 60 miles radius, then: TOTAL_SALE = SALE of 9 firms (5+3+1). The following code is also new to me. I’ve never know that I can use sashelp files as my input files!!! data _null_; set sashelp.vmember(where=(libname='WORK' and lowcase(memname) like 'new%')) end=last; if _n_ eq 1 then call execute('proc sql;'); call execute('create table _'||strip(memname)||' as select a.*,b.zip as _zip from '||memname||' as a,'||memname||' as b where .< zipcitydistance(a.zip , b.zip) <=60;'); if last then call execute('quit;'); run; Even though hash object is an efficient method, but I feel a bit difficult to code it. Thank you. Regards, mspak

mspak · ‎11-16-2012

Dear Ksharp, Thank you for your program. I ran the program, and obtained the output - want (as attached). I notice that there are missing "total_sale". This is impossible. The sales value of the firm itself should be included in the calculation of total sales. Even if there is the only one firm (that is - the firm itself) is located within 60 miles radius, the total sales should be same with the sales of that firm. Don't you think that I should sum up the "total_sale" and "sale"? Should I calculate the market share as: sale/sum(total_sale, sale)? I feel that the total_sale that you calculated is the total sales of firms within 60 miles radius from the firm (excluding the firm itself). Another question: You wrote a command as follows: if _n_ eq 1 then call execute('proc sql;'); Does this mean that proc sql is run in the memory? I never think that I can do that!!! Thank you. Regards, Mei Sen

mspak · ‎11-16-2012

Dear all, I would like to calculate the market share of a firm (tic) based on geographic area (distance based on zipcodes) for a given industry (sic3) and financial year (fyear). Market share = sales of the firm / total sales for all the firms in the same industry (sic3) in a year (fyear) within 60 miles radius. Let says, firm A, B and C are located in the same geographic area (ie within 60 miles radius). Only firm A and B are in the same industry (SIC3=123) in fyear = 2003; then market share for A in year 2003= SALE of firm A/sum(SALE of firm A, SALE of firm B) . I attached a SAS file with the all US firms from years 2003 to 2010: tic = identification code for firm fyear = financial year SALE = sales value for each firm SIC3 = industry classification code zip = zipcode of a firm (for the calculation of distance) The following procedures should be relevant: 1. Identify firms that: - in the same SIC3 - in the same FYEAR - within 60 miles radius (if .<zipcitydistance(_zip , zip) <=60) from a firm-year 2. calculate total sales of the firms identified 3. Calculate market share of the firm = salei / Total sale Note: I wish to use hash object to calculate the market share as it involves self-matching of firms. It will consume very much computer resources if proc sql to be used. Any help is much appreciated. Thank you. Regards, mspak

mspak · ‎09-25-2012

Thank you Ksharp, It is workable!!! Have a nice days! Regards, mspak

mspak · ‎09-24-2012

Dear Ksharp, It is right that there are many firms can be matched with a particular tem1.tic. If I were to delete the firms with duplicate of tem1.tic and tem1.fyear, I will get one matched firms for each tem1.tic for a given year (tem1.fyear). However, there is a possibility that the same matched firm (with tic and fyear) also a matched firm for another tem1.tic in given year (tem1.fyear). At the end, I have to ensure there are unique pairs of firms. Once there is a duplicate matched firms, I have to eliminate the duplicates, the pairing procedure is not done properly. Hope to get any suggestions on codes, if any. Thank you. Regards, mspak

Online Status	Offline
Date Last Visited	‎03-26-2017 10:35 AM

Re: Select most recent row with value

Re: Select most recent row with value

Re: Select most recent row with value

Select most recent row with value

Re: Optimal Lag length

Optimal Lag length

Creating zipcodes with FIPS codes

Re: Interpolation

Interpolation

Re: Proc Panel Warning message

Re: Select most recent row with value

Re: Select most recent row with value

Re: GMM using Proc Panel

Re: ZIPCODE FOR EACH COUNTY and INTERPOLATION

Re: Linear interpolation

Outlier detection

Re: AR test

Re: Combine Datasets using Inexact Character Variables in SAS

Combine Datasets using Inexact Character Variables in SAS

Re: Comparison between largest and second largest value

Re: Proc expand & Data step: Standard deviation and Correlation

Proc expand & Data step: Standard deviation and Correlation

Re: Comparison between largest and second largest value

Re: Comparison between largest and second largest value

Re: Comparison between largest and second largest value

Comparison between largest and second largest value

Re: Market share based on zipcodes

Re: Market share based on zipcodes

Re: Market share based on zipcodes

Re: Market share based on zipcodes

Re: Market share based on zipcodes

Re: Market share based on zipcodes

Market share based on zipcodes

Re: Matching without replacement

Re: Matching without replacement