About Saba1

Saba1 · ‎12-26-2017

@Ksharp thanks fro the help. but this code replaces the missing observations with the values at P5 or P95. I want to keep missing observations as missing.

Saba1 · ‎12-26-2017

Hi I have a data set containing three variables and I want to winsorize them at 1% and 99%. Moreover, I need to replace the observations which are less than 1% with the observation at 1%, likewise replace the observations which are greater than 99% with the observation at 99%. These three variables have missing as well as repeated observations. I am using the code below but due to missing and repeated observations, it does not give required outcome. Please guide me in this regard. %let L=1; %let H=%eval(100 - &L); %* 99th percentile*; proc univariate data=have noprint; var Size BM ME; output out=_winsor pctlpts=&L &H pctlpre=__Size __BM __ME; run; data want (drop=__:); set have; if _n_=1 then set _winsor; array wlo {*} __Size&L __BM&L __ME&L; array whi {*} __Size&H __BM&H __ME&H; array wval {*} wSize wBM wME; array val {*} Size BM ME; do _V=1 to dim(val); wval{_V}=min(max(val{_V},wlo{_V}),whi{_V}); end; run;

Saba1 · ‎12-18-2017

@novinosrin Thanks. The format is exactly the same as required. Thanks a lot.

Saba1 · ‎12-18-2017

@novinosrin Thanks a lot. By using your code, I can change the appearance of Date, i.e. it looks as 12/31/1997 but it is still "Character" with format and informat as $10. How can I change the format to be mmddyy10.?

Saba1 · ‎12-18-2017

Hi I have my data for a variable in "csv" file where each column is a Quarterly date from year 1997 to 2016 (e.g. date format is: 03/31/1997 06/30/1997 09/30/1997 12/31/1997), and each row is a company (e.g. CUSIP). I want to transpose this data in format below: CUSIP Date_Quarter Variable 001 03/31/1997 2.3 001 06/30/1997 5.0 001 09/30/1997 4.78 001 12/31/1997 4.5 002 03/31/1998 6.6 002 06/30/1998 9.0 002 09/30/1998 8.7 002 12/31/1998 5.6 and so on. I have imported the original data file in SAS, which is making Dates as Column Names and now they look like this: "_03_31_1997". This means SAS is not considering them as Dates. I have applied the following code to get required data set; proc transpose data=have out=want; by CUSIP ; run; This gives me accurate results, but the only issue is with formatting of Dates. The Date column is a Character right now with format and informat as "$11". Please guide me how to get dates format as mmddyy10. Thanks.

Saba1 · ‎12-13-2017

@Peter_C I need to use something like Ret[t-12]+Ret[t-11]+Ret[t-10]+Ret[t-9]+ Ret[t-8]+Ret[t-7]+Ret[t-6]+Ret[t-5]+ Ret[t-4]+Ret[t-3]+Ret[t-2] where "t-n" are lags. But the problem is that I need to keep rolling it for each month "t" by PERMNO.

Saba1 · ‎12-13-2017

@PeterClemmensen 1- I need to measure "past year return" and literature uses "lag t-12, lag t-2" for this variable. This means "cumulative return of past 11 months excluding the most recent month". Cumulative t-12,t-2 is the sum of lag-returns from lag 2 to lag 12. 2- Yes, I do have SAS/ETS.

Saba1 · ‎12-13-2017

@RM6 To run a regression, I need to control for past returns. One of the controls is "past year return" and literature uses "lag t-12, lag t-2" for this variable. This means "cumulative return of past 11 months excluding the most recent month". Hope it clarifies.

Saba1 · ‎12-12-2017

Hi I have a data set with monthly returns for about 4000 different firms. I want to create a new column with "cumulative past returns from month t – 12 to t − 2" for each firm separately, excluding the most recent one i.e. "t − 1". Here firm is identified by PERMNO. Please guide me how to take cumulative lag (t-12, t-2) by firm? See a sample of my data set below. Thanks. Data have; input Date: mmddyy10. PERMNO Ret; format Date mmddyy10.; datalines; 01/31/2001 21227 -0.03 02/28/2001 21227 -0.06 03/31/2001 21227 -0.04 04/30/2001 21227 0.04 05/31/2001 21227 0.12 06/30/2001 21227 -0.08 07/31/2001 21227 0.12 08/31/2001 21227 0.02 09/30/2001 21227 0.11 10/31/2001 21227 0.17 11/30/2001 21227 -0.11 12/31/2001 21227 -0.04 01/31/2002 21227 -0.2 02/28/2002 21227 0.11 03/31/2002 21227 -0.06 04/30/2002 21227 -0.03 06/30/2005 22100 -0.01 07/31/2005 22100 0.02 08/31/2005 22100 0.09 09/30/2005 22100 0.006 10/31/2005 22100 -0.07 11/30/2005 22100 0.06 12/31/2005 22100 0.09 01/31/2006 22100 0.07 02/28/2006 22100 -0.15 03/31/2006 22100 0.05 04/30/2006 22100 0.08 05/31/2006 22100 -0.01 06/30/2006 22100 -0.02 07/31/2006 22100 -0.14 08/31/2006 22100 -0.10 09/30/2006 22100 0.007 10/31/2006 22100 0.05 11/30/2006 22100 0.06 12/31/2006 22100 0.12 run;

Saba1 · ‎10-23-2017

@Ksharp Sorry to bother you again, as I have mentioned this code is giving the exact output but there are still two issues. First one is that if X1=1 more than one time for an ID, "Want" column is not equal to "X" in such a case. My objective is that for the very first time as X1=1 for an ID, remaining all observations of Want should be equal to X irrespective of "X1=1" occurs twice, thrice or even more than that in a particular ID. Secondly, I am looking for Want column where "want=x" NOT from the observation where X1=1 but from the START of the YEAR in which X1=1 for a specific ID. For example, in my data set "have" for ID1 X1=1 in a month of year 2005 (07/31/2005). So now "want=X" should be starting from the very first date of this year i.e. 01/31/2005 and should then continue till 10/31/2008. Please see below the output required: data want; input ID Date_month X X1 Want ; datalines; 1 12/31/2001 1 0 0 1 01/31/2005 0 0 0 1 02/28/2005 1 0 1 1 03/31/2005 1 0 1 1 04/30/2005 0 0 0 1 05/31/2005 0 0 0 1 06/30/2005 0 0 0 1 07/31/2005 1 1 1 1 08/31/2005 0 0 0 1 09/30/2005 1 0 1 1 10/31/2005 1 0 1 1 11/30/2005 0 1 0 1 12/31/2005 0 0 0 1 01/31/2008 1 0 1 1 02/28/2008 1 0 1 1 03/31/2008 0 0 0 1 10/31/2008 1 0 1 2 01/31/2004 1 0 1 2 02/28/2004 1 1 1 2 03/31/2004 0 1 0 2 08/31/2004 0 0 0 2 11/30/2004 1 0 1 2 12/31/2004 1 0 1; run; I will be thankful for your guidance.

Saba1 · ‎10-23-2017

@Ksharp Thank you so much. The results are exactly the same as I want. Your help is much appreciated.

Saba1 · ‎10-22-2017

@Ksharp I would like to ask for your assistance regarding the same mentioned data set. This time I need to create a new column in WANT data set where if X1=1 then all remaining observations of a particular ID should be equal to X, regardless of the month or year group. So basically for each ID, the observations of new column must be zero till X1 becomes equal to 1, and soon after X1=1 all values of that year and remaining ones for that ID should be equal to the values of X. For example, in my data set Have for ID 1, on 07/31/2005 X1=1, so from 01/31/2005 till the end of ID 1 i.e. 10/31/2008 all the values of new column should be equal to X. The same is required for all the IDs. Please guide me in this regard. Thanks.

Saba1 · ‎10-09-2017

@Ksharp I am really grateful to you. The output is exactly what I want. Thanks.

Saba1 · ‎10-09-2017

Hi I have following data set with different IDs and each ID has sorted regular monthly dates of various years. There are two variables X and X1 with 0 and 1 values based on some event. See a small sample below : data have; input ID Date_month X X1 ; datalines; 1 12/31/2001 1 0 1 01/31/2005 0 0 1 02/28/2005 1 0 1 03/31/2005 1 0 1 04/30/2005 0 0 1 05/31/2005 0 0 1 06/30/2005 0 0 1 07/31/2005 1 1 1 08/31/2005 0 0 1 09/30/2005 1 0 1 10/31/2005 1 0 1 11/30/2005 0 1 1 12/31/2005 0 0 1 01/31/2008 1 0 1 02/28/2008 1 0 1 03/31/2008 0 0 1 10/31/2008 1 0 2 01/31/2004 1 0 2 02/28/2004 1 1 2 03/31/2004 0 1 2 08/31/2004 0 0 2 11/30/2004 1 0 2 12/31/2004 1 0 ; run; Now I want to create a column which should consider each ID as a group (By Group). If the value of X1 is equal to "1" in a particular month then the values of "New" column should be equal to X, BUT ONLY for 12 months of the particular year and particular ID. So in the sample below, for ID1 the value of X1 is equal to "1" in a month of year 2005, therefore column New has the values of column X only for all the months of year 2005, rest is "0". the same is happening for ID2 and all the months of only year 2004. Please guide me in this regard. data want; input ID Date_month X X1 New ; datalines; 1 12/31/2001 1 0 0 1 01/31/2005 0 0 0 1 02/28/2005 1 0 1 1 03/31/2005 1 0 1 1 04/30/2005 0 0 0 1 05/31/2005 0 0 0 1 06/30/2005 0 0 0 1 07/31/2005 1 1 1 1 08/31/2005 0 0 0 1 09/30/2005 1 0 1 1 10/31/2005 1 0 1 1 11/30/2005 0 1 0 1 12/31/2005 0 0 0 1 01/31/2008 1 0 0 1 02/28/2008 1 0 0 1 03/31/2008 0 0 0 1 10/31/2008 1 0 0 2 01/31/2004 1 0 1 2 02/28/2004 1 1 1 2 03/31/2004 0 1 0 2 08/31/2004 0 0 0 2 11/30/2004 1 0 1 2 12/31/2004 1 0 1 ; run;

Saba1 · ‎10-06-2017

@Ksharp right, thanks.

Online Status	Offline
Date Last Visited	‎07-18-2020 08:41 PM

Re: dates of trading days

dates of trading days

Re: Repeating numbers

Repeating numbers

Re: Finding Week Number of a Month

Finding Week Number of a Month

Combine two datasets

Re: rename multiple variables with a series of numbers

rename multiple variables with a series of numbers

Re: Proc Syslin with Fixed Effects and Robust Standard Errors

Re: dates of trading days

Re: dates of trading days

Re: Repeating numbers

Re: Proc Syslin with Fixed Effects and Robust Standard Errors

Re: Finding Week Number of a Month

dates of trading days

Wald test for proc glm

Re: SAS monthly and daily Date formatting

Re: Winsorizing variables with missing observations

Winsorizing variables with missing observations

Re: Transposing Data and Date Formatting

Re: Transposing Data and Date Formatting

Transposing Data and Date Formatting

Re: Cumulative past monthly returns

Re: Cumulative past monthly returns

Re: Cumulative past monthly returns

Cumulative past monthly returns

Re: How to Conditionally repeat values of a variable by ID and by Year

Re: How to Conditionally repeat values of a variable by ID and by Year

Re: How to Conditionally repeat values of a variable by ID and by Year

Re: How to Conditionally repeat values of a variable by ID and by Year

How to Conditionally repeat values of a variable by ID and by Year

Re: cross sectional regression by month- NOT Rolling regression