Thank you Reeza. This works well.  Could someone help me with figuring out how to code the EXITDATE. EXITDATE is the last YR_QTR of positive employment (EMP >0) followed by four quarters where EMP is 0.  ... View more

Efficiency is dependent on input (that you described), on the target (what do you wand to do / analyze / report) and on resources you have (like disk space, memory available);   Another issue - is your data already in SAS tables or in external data (CSV or text or DataBase and if DB - what kind) All those have effect on programming code.   Even after getting information, as above, there maybe several ways to do the work efficiently.   If possible, describe what variables are in each file kind. Meanwhile I understand that you have 2 kinds of data: 1) a table of firms in each state  (variables: FIRMID, STATENAME) 2) employment data in (11 years X 4 quarters X number of states) files.     What are the names of those files - are they in a common format ? ... View more

You may be able to analyze these data using a mixed model approach.  GLIMMIX offers more flexibility in this, where you could model the correlation between the multiple response variables using a G side matrix, and the correlation over time using an R side matrix, with a type appropriate for uneven spacing of the data.  Can't guarantee anything though--it may be difficult to obtain convergence.   Steve Denham ... View more

Yes! Summarise each table, and *then* append the summaries where employment is non-zero. Something like %macro loop;   %do year=1980 %to 2005;     %do qtr=1 %to 4;       proc summary data=DATA_&year.Q&qtr. nway;         id YR_QTR;         class STATENAME FIRMID;         var EMPL1 EMPL2 EMPL3 ;         output out=SUM_&year.Q&qtr. sum=;       run;     %end;   %end;   data WANT;   set     %do year=1980 %to 2005;       %do qtr=1 %to 4;        &SUM_&year.Q&qtr.       %end;     %end;   ;   where EMPL1 & EMPL2 & EMPL3;   by YR_QTR STATENAME FIRMID;   ... more logic   run; %mend; %loop; ... View more