About finans_sas

finans_sas · ‎04-04-2013

Thank you for your quick response. I am interested in choosing a subset based on cday such that the first subset will include observations with cday between 0 and 21, the second subset will include observations with cday between 0 and 42, etc. Let's day that my main dataset is fullrets. I need to convert the following steps into a macro; data subset1; set fullrets; where 0 le cday le 21; data subset2; set fullrets; where 0 le cday le 42; . . . data subset6; set fullrets; where 0 le cday le 252; I am now going over the code you posted. I really appreciated your help.

finans_sas · ‎04-04-2013

Dear Community Members, I have a variable called cday, which goes from -252 to 252, denoting the number of trading days in a year. I need to choose a window such that it will increase by certain intervals, but will always start from day 0. In particular, I want to choose the following 6 intervals (windows): 0-5 0-21 0-42 0-63 0-126 0-252 Is it possible to have a %do statement where the increments are above the intervals? (something like %do i=0-5 %to 0-252) I thank in advance for your time and help, Best,

finans_sas · ‎01-29-2013

Thank you all for your amazing help. This is what I needed.

finans_sas · ‎01-28-2013

Thank you for your help and suggestions. I am going to try the code suggested by Linlin. Regarding Astounding's question, I realized that I was not clear in asking this question. By reducing the size, I actually meant reducing the number of rows. I have multiple observations for each id (obs), I want to put all of it in one single row. The reason why I am interested in this transformation is to change my dataset to a panel date setting that allows me to have dummies for each code (comcode). Best,

finans_sas · ‎01-27-2013

Dear All, I have a dataset (attached) with observation ids (obs) and a set of codes for each id (comcode). Some obs may have only one comcode and some others may have as many as 10. My goal is to transform the data the following way: FROM: obs comcode 1 251 1 182 1 134 2 251 2 134 2 182 3 186 3 251 3 134 3 182 4 134 4 182 TO: obs c102 c104 c106 ......c134......c182.......c186....... 1 0 0 0 1 1 0 2 0 0 0 1 1 0 3 0 0 0 1 1 1 . . . In other words, I want to reduce the size of the dataset by creating dummies for each code. I tried transposing the data, but it created errors due to repeating codes. I would appreciate your help. Thanks,

finans_sas · ‎01-12-2013

Thank you Anca tilea for your answer. Actually, these two datasets are composed of different variables except for the common id. Yes, I am merging one-to-many. My concern was that when I merged state.a and state.b and called the new dataset state.a again, there could be some errors in the matching process. In other words, in the presence of two different datasets with a common id (state.a and state.b), am I allowed to call their merger state.a if state.a is all what I care about? Or should I always create a new dataset as a result of this merge operation such as: data state.c; merge state.a(in=tag) state.b by id; if tag=1; run; Thank you for your help.

finans_sas · ‎01-12-2013

Dear Community Members, I have a quick question on proc merge. Suppose, I have the following datasets: state.a and state.b with a common id. When I apply data step merge, I generally create a new dataset, but recently I have been doing something like: data state.a; merge state.a(in=tag) state.b by id; if tag=1; run; The reason for not creating a new dataset was to reduce the number of sets in my sas library, but later I become concerned about such a procedure leading errors that I did not notice before. In small datasets, this procedure seems to work, but in I am not sure how it really performs in large datasets (in millions). I was wondering if you could offer some guidance on this issue. Another related question is: will the answer to my question change if these datasets were in the WORK library? Thank you for your time and help in advance.

finans_sas · ‎12-18-2012

Dear All, I have a quick question on SQL. When I executed the following codes, I get the same result (aa is identical to aaa) despite the fact that I remove "as" in the "from" line in the second code. I have read various documents on the application of proc sql and they all have "as" in the "from" line. Is "as" an unnecessary statement here? Thanks proc sql; create table aa as select a.ret, a.transaction_id, b.transaction_id as myid, b.newchamber from decode.s3_fulldata as a, decode.s3_poldata as b where a.transaction_id=b.transaction_id order by transaction_id; quit; proc sql; create table aaa as select a.ret, a.transaction_id, b.transaction_id as myid, b.newchamber from decode.s3_fulldata a, decode.s3_poldata b where a.transaction_id=b.transaction_id order by transaction_id; quit;

finans_sas · ‎11-13-2012

Thank you, PGStats for your kind help. Best,

finans_sas · ‎11-10-2012

Dear All, I am trying to calculate a 12 month moving sum for variable "frac" in the test data set I attached (there are 3000+ ids in the original data). I modified the program (below) from SAS website (http://support.sas.com/kb/25/027.html) and it seems to be working alright. However, towards the end of the sample, it is generating extremely small numbers for the moving average while it is supposed to generate zeros due to missing observations. I will really appreciate your help in understanding and fixing this output. Thanks, proc sort data=test; by id myear; data test; set test; by id; retain mnum_sum 0; if first.id then do; mcount=0; mnum_sum=0; end; mcount+1; %let m = 12; mlast&m=lag&m(frac); if mcount gt &m then mnum_sum=sum(mnum_sum,frac,-mlast&m); else mnum_sum=sum(mnum_sum,frac); if mcount ge &m then movavg=mnum_sum/&m; else movavg=.; *first 11 months are to set to missing; run;

finans_sas · ‎11-03-2012

Thank you all for your invaluable help. This is really a great community dedicated to sharing and fostering our knowledge. Best,

finans_sas · ‎10-31-2012

I just realized that my program also does not work when I have more than two portfolios. See the data set below. For example, for portid=6 and firmid=620, the average should be calculated averaging returns all of companies in portid 1 through portid 5 (where myear=200302), but all of the suggestions seem to calculate 4 or 5 different average for portid=6 and firmid=620. DATA return; INPUT portid firmid myear return; DATALINES; 1 100 200301 2.11 1 120 200301 3.12 1 130 200301 2.13 1 140 200301 3.67 1 100 200302 5.23 1 120 200302 6.81 1 130 200302 2.55 1 140 200302 4.21 1 150 200302 3.21 2 300 200301 1.11 2 320 200301 0.12 2 330 200301 -2.13 2 340 200301 -1.67 2 300 200302 4.23 2 320 200302 3.81 2 330 200302 1.55 2 340 200302 2.21 2 350 200302 4.23 2 100 200302 5.23 3 56 200301 3.11 3 100 200301 2.11 3 763 200301 5.13 3 412 200301 4.67 3 124 200302 3.23 3 97 200302 3.81 3 195 200302 1.55 4 740 200302 0.21 4 250 200302 -1.21 4 812 200301 -1.11 4 823 200301 7.12 5 835 200301 -3.33 5 947 200301 -4.67 5 980 200302 4.23 6 620 200302 3.81 ; run;

finans_sas · ‎10-30-2012

Thank you for your further explanations. In this project, I have 50 portfolios, but sometimes it gets as high as 400. I should then be careful about the portfolio size.

finans_sas · ‎10-30-2012

Thank you Arthur (art297) for spending your valuable time helping me out. Your updated version indeed generates the desirable output. However, it will take me a while to digest what exactly is going on in the code and to make sure that I can comfortably apply to my large data set. I am really sorry that I have not made my inquiry very clear. Maybe the story below may shed some light: Imagine that you have two portfolios (portid 1 and 2) and company A is in both portid 1 and portid2. Portid 1 also contains Companies B and C. Portid 2 contains Companies D and E. In short, Portid 1: A, B, C Portid 2: A, D, E I am interested in calculating an average value for each company by averaging returns of companies in other portfolios. For example, average for C: (A+D+E)/3, all of which come from portid 2 (A is common) average for E: (A+B+C)/3, all of which come from portid 1 average for A in portid 1= (D+E)/2 average for A in portid 2=(B+C)/2 Overall, I have two rules: a company's average return must exclude its own return (as in A) and must only include returns from other portfolios. I hope my objective is clearer now.

finans_sas · ‎10-30-2012

Below is what you have requested (based on the code I have). I hope this helps. The program works great, but takes a long time to process. DATA return; INPUT portid firmid myear return; DATALINES; 1 100 200301 2.11 1 120 200301 3.12 1 130 200301 2.13 1 140 200301 3.67 1 100 200302 5.23 1 120 200302 6.81 1 130 200302 2.55 1 140 200302 4.21 1 150 200302 3.21 2 300 200301 1.11 2 320 200301 0.12 2 330 200301 -2.13 2 340 200301 -1.67 2 300 200302 4.23 2 320 200302 3.81 2 330 200302 1.55 2 340 200302 2.21 2 350 200302 4.23 2 100 200302 3.66 ; run; proc transpose data=return out=need (drop=_name_); var return; id firmid; by portid myear; run; data need; set need; rename myear=myear2; rename portid=portid2; run; proc sql; create table want as select * from return, need where return.myear=need.myear2 and return.portid ne need.portid2; quit; data want (drop=i _:);; set want; array returns(*) _100--_350; do i=1 to dim(returns); if input(substr(vname(returns(i)),2),8.) eq firmid then call missing(returns(i)); end; average=mean(of returns(*)); run; proc sort data=want; by portid myear firmid; run;

Online Status	Offline
Date Last Visited	‎09-21-2023 08:03 PM

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Re: SAS Macro & Nested Do Loop to subset data

Choosing the Earliest and Latest Dates

Re: Do Loops that start from the same location

Do Loops that start from the same location

Re: Reducing the size of the data via dummies

Re: Reducing the size of the data via dummies

Reducing the size of the data via dummies

Re: Proc Merge on Existing Datasets

Merge on Existing Datasets

SQL Question

Re: Moving Average with Missing Observations

Moving Average with Missing Observations

Re: Proc SQL with Large Data Sets

Re: Proc SQL with Large Data Sets

Re: Proc SQL with Large Data Sets

Re: Proc SQL with Large Data Sets

Re: Proc SQL with Large Data Sets