Thanks. I tried that before but somehow with your code it's working. ... View more

Hello Reeza, I create a variable that combines both contracts and dates (I hope this is what you meant by uniquely identifying a timepoint) and I ran the GMM. However, I still get an error message. Do I need to have an instrument variable?? Here a copy of the log: 246  proc panel data =s_basint15; 247  id cntd int; 248  model BASw = tod1-tod33 /gmm twostep maxband=5 nolevels noint; 249  run; NOTE: GMM has been requested without an associated instruments statement. PROC PANEL will       estimate under the assumptions of strict exogeneity and non-correlatedness in the right       hand side variables. ERROR: The required statement INSTRUMENTS is missing for the model . This model will be        skipped. NOTE: PROCEDURE PANEL used (Total process time):       real time           0.15 seconds       cpu time            0.14 seconds Thank you very much for your help. ... View more

Thank you a lot for your help, Reeza! How do you suggest I create a variable to uniquely identify a timepoint?? ... View more

I am not sure what you mean by duplicate ID's. Could you please explain? I have intraday data so the time series int =1 to 33 is repeated in different days for each contracts. Would that be the problem? ... View more

Here you go: 707  proc sort data=s_basint15; 708  by cnt int date; 709  run; NOTE: There were 70884 observations read from the data set WORK.S_BASINT15. NOTE: The data set WORK.S_BASINT15 has 70884 observations and 43 variables. NOTE: PROCEDURE SORT used (Total process time):       real time           1.88 seconds       cpu time            0.39 seconds 710  proc panel data =s_basint15 plot(unpackpanel)=all; 711  id cnt int; 712  model BASw = tod1-tod33 /gmm twostep maxband=5 nolevels noint; 713  run; NOTE: GMM has been requested without an associated instruments statement. PROC PANEL will       estimate under the assumptions of strict exogeneity and non-correlatedness in the right       hand side variables. ERROR: Data set WORK.S_BASINT15 is not sorted in ascending sequence with respect to time        series ID.  The current time period has int=1 and the previous time period has int=1 in        cross section cnt=ABE. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE PANEL used (Total process time):       real time           0.02 seconds       cpu time            0.01 seconds ... View more

Hello Reeza, Thanks for your suggestion but I tried sorting my data by cnt int date but it is always bringing back the same error! ... View more

Thank you all for taking time to answer my question. Both Arthur's modifications and Tom's code are working perfectly. I will go ahead and give Tom "correct answer" since it has the full code for other people to use. Again thanks to everyone for your precious assistance! ... View more

Hello Tom, The modification worked perfectly! Thank you! ... View more

Oh yes it is working now. I had the excel file open so that's probably why I got an error message. Thanks ... View more

Hello Arthur, I tried changing the compress(NEMNAME ... bit My full code is now: %macro excelbook; libname D 'F:\phdsas\Book1.xls'; proc sql noprint; select count (distinct (memname)) INTO :TOT from sashelp.vtable where libname='D' ; SELECT DISTINCT (memname) into :s1-:s%trim(%left(&tot)) from sashelp.vtable where libname='D'; select distinct(translate(trim(compress(MEMNAME,"'$")),'_',' ')) into: v1 - :v%trim(%left(&tot)) from sashelp.vtable where libname='D'; quit; libname D clear; %DO I=4 %TO &tot; %put &&v&i; %put &&s&i; proc import DATAfile= 'F:\phdsas\Book1.xls' out=&&v&I  replace;    RANGE="&&s&i";    getnames=NO;    run; DATA &&v&I; SET &&v&I; TICKER="&&v&I"; RUN; proc append base=all data=&&v&I force; run; %END; %mend; %excelbook; The error message I got back is: WARNING: Apparent symbolic reference TOT not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric        operand is required. The condition was: &tot ERROR: The %TO value of the %DO I loop is invalid. ERROR: The macro EXCELBOOK will stop executing. ... View more

Hello Tom, Thanks for your help. I ran the code you gave me and here is the error message. For example for sheet DPW US EQUITY: DPW US Equity           --           22           202 ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,               BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT. ERROR 202-322: The option or parameter is not recognized and will be ignored. The do loop starts from 4 because my data is in sheet 4 onward. Should I change the select memname        , compress(memname,"'$")     into :s1-:s9999 ? ... View more

Hi everyone, Thank you all for your assistance. Now it is not working at all. The code worked just fine when I have the sheet numbered, but when I am using sheet names with tickers, it is not working at all. Here is the error code it brings back when I run the code For example sheet "AMZG US EQUITY": 1    AMZG US Equity                  --                22               202 ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,               BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT. Is there a problem with the sheet name? ... View more