Hi,  I am trying to import Excel file into SAS and i have some issues. More specifically, one of the Excel columns contains a mixed of number and character/numbers values (see below) . The dataset is quite big with more than 100k raws. I use proc import to import to SAS however, SAS has replaced all the character-number values with dots. ( for example, instead of  20HA in SAS i can see .  (dots)) .   I read an article that for DBMS=EXCEL or EXCELCS i need to use the MIXED=Yes option which i did it but i still get an error that the MIXED statement is not valid or used out of proper order   Below you can see my code and SAS log   11319 11319 11319 11319 11319 11319 20HA 20HA 20HA 20HA 20HA   proc import out = data_raw dbms = excelcs datafile = "C:\Documents\data.xlsx" replace; sheet = "data"; version = "2002"; server = "servername"; port = 8958; ServerUser = "&userID"; ServerPass = "&pass"; mixed=YES; run;   LOG __________________________________________________________________________   33 mixed=YES; _____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 34 run;   ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1c2c Thread 0x1d40 DBC 0x21a84e8 Excel'. ERROR: Error in the LIBNAME statement. Connection Failed. See log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time):       ... View more

hi, i am quite new with SAS and i am struggling to understand the ADF process for selecting the correct number of lags   As a first step for the ADF test i tried to identify the correct number of lags that i have to include. I use monthly data, therefore i added ADF=12 lags and run the below code  :    ***ADF test***; PROC ARIMA DATA= rethouse_reg1 ; IDENTIFY VAR = DEPPC_forc  STATIONARITY=(ADF=12) ; RUN; QUIT;   then i looked at the ADF and Trend Correlation analysis  :    i looked at the trend and correlation analysis graph (see below). My series doesn't have trend (based on the graph) and has mean different than zero (based on table below mean = 6.381432) therefore, i concluded that i have to use the single mean in ADF.   After this i looked at the ADF table (see at the bottom) in the single mean model and starting from the bottom (12 lags) I found that the 9 lags are statistical significant (0.0146<0.05). Based on this i concluded that i have to use 9 lags in the ADF test.    Then i looked at the ACF graph and counted the number of columns which are outside the shed blue area and found 11lags and i understood that i had to include 11lags for the ADF (compare to 9lags that found earlier)   my questions are :   1) Is the steps that i followed correct for selecting the appropriate number of lags for the ADF?  2) If yes, which one of the above results for the correct number of lags i can trust and include in my ADF test and why?    Name of Variable = DEPPC_forc Mean of Working Series 6.381432 Standard Deviation 2.35614 Number of Observations 277         ugmented Dickey-Fuller Unit Root Tests Type Lags Rho Pr < Rho Tau Pr < Tau F Pr > F Zero Mean 0 0.4318 0.7889 0.37 0.792       1 0.3733 0.7738 0.3 0.7728       2 -0.0458 0.6719 -0.03 0.6726       3 -0.5249 0.5642 -0.3 0.5756       4 -0.6041 0.5474 -0.34 0.5629       5 -0.595 0.5493 -0.33 0.564       6 -0.6329 0.5414 -0.34 0.561       7 -0.8189 0.5049 -0.42 0.5322       8 -1.4697 0.3989 -0.65 0.4358       9 -1.5907 0.3821 -0.68 0.422       10 -0.8402 0.5009 -0.43 0.5252       11 -0.5906 0.5502 -0.33 0.5657       12 -0.145 0.6494 -0.12 0.641     Single Mean 0 -2.7088 0.6911 -0.81 0.8142 0.57 0.9306   1 -3.6673 0.5752 -1.01 0.7512 0.74 0.8816   2 -8.616 0.1846 -1.81 0.3773 1.82 0.6057   3 -12.848 0.0651 -2.2 0.2058 2.52 0.4246   4 -15.2504 0.0354 -2.34 0.1595 2.84 0.3433   5 -15.8331 0.0305 -2.33 0.1647 2.79 0.3552   6 -19.2865 0.0127 -2.49 0.1191 3.21 0.2497   7 -25.4427 0.0028 -2.72 0.0731 3.78 0.1015   8 -45.8484 0.0015 -3.18 0.0228 5.1 0.0347   9 -64.8018 0.0015 -3.34 0.0146 5.61 0.0205   10 -35.3822 0.0015 -2.8 0.0601 4 0.0875   11 -29.8813 0.0015 -2.61 0.0917 3.51 0.1724   12 -9.0271 0.1669 -1.7 0.4324 1.5 0.6861 Trend 0 -1.8741 0.9729 -0.55 0.9808 0.95 0.9735   1 -2.8408 0.9428 -0.76 0.9668 0.89 0.9788   2 -8.555 0.5357 -1.72 0.7411 1.62 0.8528   3 -13.339 0.2423 -2.14 0.5208 2.43 0.6899   4 -16.6115 0.1298 -2.32 0.423 2.8 0.6158   5 -17.441 0.1099 -2.29 0.4385 2.76 0.6237   6 -23.2314 0.0324 -2.53 0.3138 3.29 0.5173   7 -34.3311 0.0025 -2.82 0.1909 4.05 0.3633   8 -82.3673 0.0006 -3.35 0.0604 5.71 0.0814   9 -210.116 0.0001 -3.58 0.0332 6.51 0.0441   10 -70.2109 0.0006 -2.99 0.136 4.58 0.2574   11 -59.658 0.0006 -2.82 0.1932 4.05 0.3632   12 -10.7044 0.3831 -1.6 0.7915 1.48 0.8809 ... View more

hi, i am quite new with SAS and i am struggling to understand the ADF process for selecting the correct number of lags   As a first step for the ADF test i tried to identify the correct number of lags that i have to include. I use monthly data, therefore i added ADF=12 lags and run the below code  :    ***ADF test***; PROC ARIMA DATA= rethouse_reg1 ; IDENTIFY VAR = DEPPC_forc  STATIONARITY=(ADF=12) ; RUN; QUIT;   then i looked at the ADF and Trend Correlation analysis  :    i looked at the trend and correlation analysis graph (see below). My series doesn't have trend (based on the graph) and has mean different than zero (based on table below mean = 6.381432) therefore, i concluded that i have to use the single mean in ADF.   After this i looked at the ADF table (see at the bottom) in the single mean model and starting from the bottom (12 lags) I found that the 9 lags are statistical significant (0.0146<0.05). Based on this i concluded that i have to use 9 lags in the ADF test.    Then i looked at the ACF graph and counted the number of columns which are outside the shed blue area and found 11lags and i understood that i had to include 11lags for the ADF (compare to 9lags that found earlier)   my questions are :   1) Is the steps that i followed correct for selecting the appropriate number of lags for the ADF?  2) If yes, which one of the above results for the correct number of lags i can trust and include in my ADF test and why?      Name of Variable = DEPPC_forc Mean of Working Series 6.381432 Standard Deviation 2.35614 Number of Observations 277   Augmented Dickey-Fuller Unit Root Tests Type Lags Rho Pr < Rho Tau Pr < Tau F Pr > F Zero Mean 0 0.4318 0.7889 0.37 0.792       1 0.3733 0.7738 0.3 0.7728       2 -0.0458 0.6719 -0.03 0.6726       3 -0.5249 0.5642 -0.3 0.5756       4 -0.6041 0.5474 -0.34 0.5629       5 -0.595 0.5493 -0.33 0.564       6 -0.6329 0.5414 -0.34 0.561       7 -0.8189 0.5049 -0.42 0.5322       8 -1.4697 0.3989 -0.65 0.4358       9 -1.5907 0.3821 -0.68 0.422       10 -0.8402 0.5009 -0.43 0.5252       11 -0.5906 0.5502 -0.33 0.5657       12 -0.145 0.6494 -0.12 0.641     Single Mean 0 -2.7088 0.6911 -0.81 0.8142 0.57 0.9306   1 -3.6673 0.5752 -1.01 0.7512 0.74 0.8816   2 -8.616 0.1846 -1.81 0.3773 1.82 0.6057   3 -12.848 0.0651 -2.2 0.2058 2.52 0.4246   4 -15.2504 0.0354 -2.34 0.1595 2.84 0.3433   5 -15.8331 0.0305 -2.33 0.1647 2.79 0.3552   6 -19.2865 0.0127 -2.49 0.1191 3.21 0.2497   7 -25.4427 0.0028 -2.72 0.0731 3.78 0.1015   8 -45.8484 0.0015 -3.18 0.0228 5.1 0.0347   9 -64.8018 0.0015 -3.34 0.0146 5.61 0.0205   10 -35.3822 0.0015 -2.8 0.0601 4 0.0875   11 -29.8813 0.0015 -2.61 0.0917 3.51 0.1724   12 -9.0271 0.1669 -1.7 0.4324 1.5 0.6861 Trend 0 -1.8741 0.9729 -0.55 0.9808 0.95 0.9735   1 -2.8408 0.9428 -0.76 0.9668 0.89 0.9788   2 -8.555 0.5357 -1.72 0.7411 1.62 0.8528   3 -13.339 0.2423 -2.14 0.5208 2.43 0.6899   4 -16.6115 0.1298 -2.32 0.423 2.8 0.6158   5 -17.441 0.1099 -2.29 0.4385 2.76 0.6237   6 -23.2314 0.0324 -2.53 0.3138 3.29 0.5173   7 -34.3311 0.0025 -2.82 0.1909 4.05 0.3633   8 -82.3673 0.0006 -3.35 0.0604 5.71 0.0814   9 -210.116 0.0001 -3.58 0.0332 6.51 0.0441   10 -70.2109 0.0006 -2.99 0.136 4.58 0.2574   11 -59.658 0.0006 -2.82 0.1932 4.05 0.3632   12 -10.7044 0.3831 -1.6 0.7915 1.48 0.8809               ... View more

Hi I have a time series, which i check it for stationarity using ADF test and arima proc    proc arima data=&td19; identify var=interest_rate stationarity=(adf=4); run; quit;   Below is the ADF test and trend & correlation analysis    i started from the table from the bottom (trend model) and using the Tau compared it with 5% until to reject the null. i concluded that the series is stationary since 0.0315<0.05 zero mean model   However, when i see the ACF/PACF, based on my understanding, the series in not stationary. This is because the ACF decays slowly   My questions are : is this series stationary or not?   Augmented Dickey-Fuller Unit Root Tests Type Lags Rho Pr < Rho Tau Pr < Tau F Pr > F Zero Mean 0 -2.7655 0.2517 -2.46 0.014       1 -5.0099 0.1214 -2.26 0.0237       2 -3.982 0.168 -2.21 0.027       3 -5.0286 0.1207 -2.27 0.0228       4 -4.2696 0.1532 -2.14 0.0315     Single Mean 0 -2.5618 0.7064 -1.71 0.4237 3.03 0.2995   1 -6.4379 0.3047 -2.17 0.2186 2.78 0.3619   2 -4.6272 0.4645 -1.92 0.3218 2.49 0.437   3 -6.5225 0.2983 -2.17 0.2192 2.8 0.3657   4 -5.2232 0.4056 -1.93 0.315 2.39 0.4685 Trend 0 -3.4578 0.9132 -1.23 0.8994 1.52 0.8742   1 -16.6892 0.1154 -2.83 0.1917 4.32 0.3133   2 -10.7554 0.3659 -2.18 0.4973 2.8 0.6186   3 -19.6884 0.0597 -2.79 0.2033 4.29 0.3316   4 -15.4981 0.1473 -2.39 0.3834 3.22 0.5402       ... View more

Hi I have a time series, which i check it for stationarity using ADF test and arima proc    proc arima data=&td19; identify var=interest_rate stationarity=(adf=4); run; quit;   Below is the ADF test and trend & correlation analysis    i started from the table from the bottom (trend model) and using the Tau compared it with 5% until to reject the null. i concluded that the series is stationary since 0.0315<0.05 zero mean model   However, when i see the ACF/PACF, based on my understanding, the series in not stationary. This is because the ACF decays slowly   My questions are : is this series stationary or not?   Augmented Dickey-Fuller Unit Root Tests Type Lags Rho Pr < Rho Tau Pr < Tau F Pr > F Zero Mean 0 -2.7655 0.2517 -2.46 0.014       1 -5.0099 0.1214 -2.26 0.0237       2 -3.982 0.168 -2.21 0.027       3 -5.0286 0.1207 -2.27 0.0228       4 -4.2696 0.1532 -2.14 0.0315     Single Mean 0 -2.5618 0.7064 -1.71 0.4237 3.03 0.2995   1 -6.4379 0.3047 -2.17 0.2186 2.78 0.3619   2 -4.6272 0.4645 -1.92 0.3218 2.49 0.437   3 -6.5225 0.2983 -2.17 0.2192 2.8 0.3657   4 -5.2232 0.4056 -1.93 0.315 2.39 0.4685 Trend 0 -3.4578 0.9132 -1.23 0.8994 1.52 0.8742   1 -16.6892 0.1154 -2.83 0.1917 4.32 0.3133   2 -10.7554 0.3659 -2.18 0.4973 2.8 0.6186   3 -19.6884 0.0597 -2.79 0.2033 4.29 0.3316   4 -15.4981 0.1473 -2.39 0.3834 3.22 0.5402       ... View more

hi, i have a general question around cointegration method. I am trying to understand if it is correct or not to use stationary variables in cointegration ?    Based on my understanding, we use cointegration in order to avoid differencing the variables and lose some of the information. Therefore we must not use stationary variables in cointegration. Is this correct?    Thanks,   Ant ... View more

hi i am trying using a macro do loop to populate some variables.   More specifically, when i run the code (see below) for 10 loops the observations are moving down in data (see below this is after 10 times loop) which is not what i need.   I need in every loop to add at the end of each column the additional observations from the calculations inside the macro.    For example, at the beginning of the 2nd loop var17 must have all its observations plus the additional which created from the 1st loop. however, this is not happening. What it happens is var17 moved on step down   Any help it would be appreciated 🙂    Obs date var17 var18 var15 1 31/03/2000 . . . 2 30/06/2000 . . . 3 30/09/2000 . . . 4 31/12/2000 . . . 5 31/03/2001 . . . 6 30/06/2001 . . . 7 30/09/2001 . . . 8 31/12/2001 . . . 9 31/03/2002 . . . 10 30/06/2002 . . . 11 30/09/2002 . . . 12 31/12/2002 . . . 13 31/03/2003 . . . 14 30/06/2003 . . . 15 30/09/2003 . . . 16 31/12/2003 . . . 17 31/03/2004 . . . 18 30/06/2004 . . . 19 30/09/2004 . . . 20 31/12/2004 . . . 21 31/03/2005 . . . 22 30/06/2005 . . . 23 30/09/2005 -0.7979 0.07234 -0.41651 24 31/12/2005 -0.3274 -0.04446 0.10851 25 31/03/2006 0.0896 -0.01812 -0.28162 26 30/06/2006 -0.3205 0.02743 0.02995       %MACRO example(COUNT); %DO I = 1 %TO &COUNT; %DO X = 2 %TO &COUNT;   data data&x; set data&i; &var17=lag1(&var15); run;   PROC SQL; UPDATE data&x SET &var24=&var17*(-0.1487); UPDATE data&x SET &var18=&var22 + &var23 + &var24 + d1_co + d2_co + d3_co; UPDATE data&x SET &var6= &var18; QUIT;   data data&x; set data&x; &var5=lag1(&var5)+ &var6; &var15= &var5 - &var16; run; %end; %END; %MEND example;   %example(10); ... View more