Dear All, Below is a portion of my data set. The code below does what I need to accomplish (which greatly benefited from your help in the past): merge these two data sets (return and need), but make sure that myear is the same, but portid is not the same. The actual sizes of the data sets are 961,896 rows X 12 columns for return, and 15,600 rows X 3,085 columns for need. When I use the actual data, the program runs for over 24 hours and then says it has to stop due to lack of hard disk space (after creating a temporary file of about 300 gb). Is there a waPry to run this PROC SQL part more efficiently (or avoid it)? Thank you **************************************************************************************************; 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; ************************************************************************************************************; proc sql;   create table want as   select *   from return a, need b     where a.myear=b.myear and a.portid ne b.portid; quit; **********************************************************************************************************; ... View more

Dear SAS Community Member, I have attached a preview of the data set I am going to ask you about. First, below is a brief description: Data Description: inames= industry names associated with each id myear=month-year pair id=firm id returns= returns associated with a given firm id last three columns give us industry names with their returns in a given month (independent of firm id. Originally matched with firm ids based on myear.) I actually have 5000 firm_ids, 49 industry names, myear goes up 200812. My Goal: Run a simple time series regressions, regressing returns on each industry returns Exclude cases where a firm's industry (inames) is the same as the industry in the columns. For example, in the data set provided, run id=2's returns against agric and chem, but banks. On the other hand, run id=1's returns against all of the three industries. Save R-squared (and others) from each regression and choose the model with the highest R-squared. My Progress: Initially, I paired each stock id with all of the industry returns (72 months X 5000 companies X 49 industries) and ended up with millions of observations. Then, I removed the cases where inames are the same as the industry names, using datastep. This appeared to be a very tedious way of accomplishing my goal and I started writing a SAS macro, where I got stock. Below is the outline of the program. It does not work at all, but I just wanted to give it a try. I would really appreciate your help to get it work. *First impose the exclusion criterion via arrays; data example ;   set example;   array industry(*) agric--chem;   do i=1 to dim(industry);     if input(vname(industry(i)) eq inames then call missing(industry(i));     *There seems to be an issue at this step. ;   end;   run; %macro industry;         %do m=1 %to 2;          data reg&m ; set example; where id=&m;  *create a separate data set for each id;                     array industry(*) agric--chem;  *introducing arrays in each data set, but I do not know how exactly I should do that;                      do i=1 to dim(industry);                     proc reg data=reg&m noprint outest=out tableout;                     model returns =industry(i) ; *I want to store r-squared and others for each industry-id pair, but again I am not sure how to accomplish it;                                           data adjr t r; set out;                                          if _type_= 'adjrsq' then output adjr;      .                                          if _type_='parms' then output p;                                          if _type_='t' then output t;                                          if _type_='rsq' then output r;                                         data &myoutput; set adjr p t r ; industry;                       end; run;                                       proc append base=stats data=&myoutput; run;               %end; %mend; %industry; ... View more

Dear Community Members, I have a dataset that looks like the following: 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 ; run; My objective is to calculate average returns for each portid and myear (i.e. average "return" in each portid-myear pair). Next, match each firmid in a given portid and myear with  the corresponding average returns. Normally, I can use "proc means" by "portid myear", but there is a restriction such that a company's return should be excluded from the corresponding average return. For example, for portid=1, myear=200301, firmid=100, the corresponding average return=(3.12+2.13+3.67)/3=2.9733333 for portid=1, myear=200301, firmid=120, the corresponding average return=(2.11+2.13+3.67)/3=2.6366666 for portid=2, myear=200302, firmid=350, the corresponding average return=(4.23+3.81+1.55+2.21)/4=2.95 I would really appreciate your help with this problem. ... View more

Dear SAS Community Members, I have the dataset below: id    term    project        start                  finish 1     10        200          20070118         20081228 1     10        197          20070324         20080624 1     10        201          20070104         20081203 1     11        200          20080101         20091124 1     11        201          20080104         20091203 2     ..... 2 2 . . . 433 What I want to do is to pick the earliest start and latest finish date for each id-term pair: id    term           start                  finish 1     10              20070104         20081228 1     11              20080101         20091203 2     ...... 2 2 . . . 433 I am not quite sure how to choose these dates. I will really appreciate your help. Thank you ... View more

Dear SAS Community Members, I would like to compute the mean of "returns" variable below and its t-statistics by dummy variables. In other words, I want to find out what the average return is when onemonth=1 and onemonth=2. I actually have 8 dummies and I am aware of the fact that I can create 8 subsets of my data for each dummy. I was just wondering if there was a more efficient way to accomplish this task. month return  signal    onemonth   secondmonth 1          0.25   down     0                   0 2          0.41   up         1                   0 3          -0.27 down      0                   1                   4          -0.37  up         1                   0 5           -0.24  up        0                   1          6          0.24    up        0                   0 Thanks ... View more