Hi, I have below program that works, but I would like to make a slight modification. Currently, the program counts the number of months between next month and a year after from this month and takes this value as 'niter' to do a regressions in a loop. Modifications I would like to make are: 1. Instead of counting months based on today() function, I would like to use macro variable defined as "&enddate". (Whenever I include "&enddate" instead of "today()", SAS produces an error.) 2. Instead of showing Horizon&n as a regression title, I want to show the forecasting date which is calculated by forecastdate=intnx('month',&enddate,&n) (I tried to define a macro variable "forecastdate" and refer to it as a regression title, but it always results in error because "&n" cannot be read by SAS although it works in the other sections of the code.) I was reading macro section of SAS textbook and trying different ways, but it seems I cannot figure this out myself.. I appreciate if someone could help me with the code. Thanks! %let enddate='01Dec2013'd; /*Read the dataset in SAS*/ data test; format date yymmd.; infile datalines missover; input D$ Price Volume A B; date = input(cats("01-", D), date9.); drop D; datalines; Jul-12 67 4 0 1 Aug-12 33 65 1 1 Sep-12 91 15 0 1 Oct-12 30 35 0 1 Nov-12 52 45 0 0 Dec-12 100 89 0 1 Jan-13 89 20 1 1 Feb-13 19 8 0 0 Mar-13 8 10 1 1 Apr-13 73 57 0 0 May-13 38 76 1 0 Jun-13 33 55 1 1 Jul-13 67 4 0 1 Aug-13 33 65 1 1 Sep-13 91 15 0 1 Oct-13 30 35 0 1 Nov-13 52 45 0 0 Dec-13 100 89 0 1 Jan-14 39 39 1 1 Feb-14 100 92 1 0 Mar-14 86 46 1 0 ; /*Determine the number of iterations*/ /*Create lagged variables and run regressions in a loop*/ %macro regression; data _null_;      call symputx('niter',intck('month', mdy(month(today())+1,01,year(today())), mdy(12,01,year(today())+1)));      run;        %do n=1 %to &niter;             data data_horizon&n;             set test;             A_L=lag&n(A);             B_L=lag&n(B);   proc reg data=data_horizon&n outest=Reg_Horizon&n TABLEOUT; Horizon&n: model Price=A B A_L B_L/NOPRINT ADJRSQ RSQUARE; Horizon&n: model Volume=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;         %end;       run; %mend; %regression /*Consolidate all the regression outputs into a single dataset*/ %macro combine; Data Regression_output; set     %do i = 1 %to &niter;       Reg_horizon&i       %end;       ;       run;       %mend; %combine ... View more

Hi everyone, I wrote below macro and it works fine as long as I have the counter n as a hard-coded number like 20. but I would like this macro to be more flexible and I want to insert INTCK function to automatically count the number of iterations from last month to the end of next year. So in the 2nd macro, I inserted the INTCK function following the examples in this forum but it produces an error. My data is monthly and INTCK function works fine itself but not within a loop. I appreciate if anyone could point out how to fix this problem. ORIGINAL %macro lag; %do n=1 %to 20;   data data_horizon&n;   set data_horizon0;   A_L=lag&n(A);     %end; run; %mend; %lag FLEXIBLE %macro lag; %do n=1 %to %sysfunc(intck('month', mdy(month(today())-1,01,year(today())), mdy(12,01,year(today())+1)));   data data_horizon&n;   set data_horizon0;   A_L=lag&n(A);     %end; run; %mend; %lag ... View more

Hi I have 2 sets of monthly sales data: Master data where all the updates need to be made Updated data ABC variables are the name of each store and value in the table represent each store's monthly sales. Each store has different opening date, so sales figures start from various timing depending on when it opened. Master sales data Date A B C Jan-13 89 Feb-13 19 Mar-13 8 Apr-13 73 57 May-13 38 76 Jun-13 33 55 Jul-13 67 4 Aug-13 33 65 Sep-13 91 15 Oct-13 30 35 Nov-13 52 45 3 Dec-13 100 89 12 Jan-14 39 39 9 Feb-14 100 92 65 Mar-14 86 46 61 Updated sales data Date A B C Jan-13 80 Feb-13 80 Mar-13 80 Apr-13 80 57 May-13 80 76 Jun-13 33 55 Jul-13 67 4 Aug-13 33 65 Sep-13 91 15 Oct-13 30 35 Nov-13 45 3 Dec-13 100 89 12 Jan-14 39 39 9 Feb-14 100 92 65 Mar-14 86 61 There are 2 data cleaning steps I coded in SAS: 1. Update master data table based on the updated data ONLY when the updated value is missing or blank. Value in the master data should NOT be modified if update is numerical value. 2. Replace sales figure with a missing value or blank for the first 3 months of store opening as the initial sales period is considered unstable I did my best to follow the textbook in writing codes below, but I cannot resolve the errors... I am guessing my array statement has an issue. If someone could let me know how I can resolve the issue, I really appreciate your help! Below is the codes I wrote for step 1. data work.master_sales; set work.updated_sales; modify work.master_sales work.updated_sales; array consistent(*)_numeric_;                  /*I did not list all variables because there are over 100 stores in the real data set*/ if consistent(*)_numeric_=' '; by date; run; Below is for step 2. data work.master_sales; set work.master_sales; array firstyear(*)_numeric_;              do i=1 to 3 while (firstyear(i) ne' ');  /*Replace sales figure with ' ' for the first 3 months of store opening*/ firstyear(i) = ' '; end; drop i; ... View more

Hi, I have a column called "Month" and they are stored in character value as below. I want to convert them into numeric format so that I can create a new variable called "date" to store SAS date value. However, it is giving me an error "ERROR 48-59: The format $MONTHFMT was not found or could not be loaded." Below is the code I wrote and I really appreciate if someone let me know how I can run the code without an error. The first set of code to specify format run without an error. The second set of code seem to have an issue. The third set of code works if I replace "Month" with any numeric characters. work.data_month Year Month 1999 January 1999 December 2000 February 2001 July libname mylib 'C:\Users'; proc format lib=mylib; value $monthfmt 'January' = 1 'February' = 2 'March' = 3 'April' = 4 'May' = 5 'June' = 6 'July' = 7 'August' = 8 'September' = 9 'October' = 10 'November' = 11 'December' = 12; run; libname mylib 'C:\Users'; data work.data_month; set work.data_month; format Month $monthfmt.; run; data work.data_month; set work.data_month; date = mdy(Month, 1, Year); format date mmddyy10.; run; ... View more