Thanks. I will recode the macro again as you mentioned.... ... View more

Options symbolgen mprint mlogic; %Global Today Today_mmddyyn8  mmdd Currentweek Currentmonth  CurrentQTR Currentyear Currentqtryear firstweek_count firstCalDayOfQTR_Date9 FirstCalDayofLastYear LastCalDayofLastQTR weekly_dsvars; %Macro test/MINOPERATOR;     %Let i=1;     Data qtr_dataset;                     %If %eval(&Currentmonth. in 4 7 10 ) %Then %Do;                         firstCalDayOfMonth = intnx('month',&Today.,0);                         firstCalDayOfQTR_Date9 = Put(firstCalDayOfMonth,Date9.);                         LastCalDayofLastQTR = Put(intnx('month',&Today.,-1,'end'),mmddyy10.);                         FirstCalDayofLastYear = Put(intnx('month',&Today.,-13),mmddyy10.);                         firstweek_count = Week(firstCalDayOfMonth);                         Call symputx("firstweek_count", firstweek_count);                         Call symputx("firstCalDayOfQTR_Date9", firstCalDayOfQTR_Date9);                         Call symputx("LastCalDayofLastQTR", LastCalDayofLastQTR);                         Call symputx("FirstCalDayofLastYear", FirstCalDayofLastYear);                     %end; %Put &firstweek_count. &LastCalDayofLastQTR. &FirstCalDayofLastYear. &firstCalDayOfQTR_Date9.; %Mend; %test; The above macro is running fine and the macro variables are being created without any error and the dataset has been created successfully. %Let CurrentQTR =2; %Let Currentmonth = 4; %Macro Create_weekly_varlist;     %Let i=1;                Data qtr_wkly_varlist;             %If &CurrentQTR. = 1 %Then %Do;                 %Let weekly_dsvars = qtr1_wkly_varlist;                 %Do %while ( &i. LE &Currentweek.);                     If &i. ne &Currentweek. Then Do;                         var3 = "week&i._itemfile";                         var4 = "week&i._vsi";                         Output;                     end;                     else Do;                         new_var1 = "week&i._itemfile";                         new_var2 = "week&i._vsi";                         weekly_count1 = Left(Trimn(%eval(&i.-1)));                         weekly_count2 = Left(Trimn(&i.));                         Output;                     end;                       %Let i = %eval(&i.+1);                  %end;              %end;             %else %If &CurrentQTR. GT 1 %Then %Do;                                 %If %eval(&Currentmonth. in 4 7 10 ) %Then %Do;                         firstCalDayOfMonth = intnx('month',&Today.,0);                         firstCalDayOfQTR_Date9 = Put(firstCalDayOfMonth,Date9.);                         LastCalDayofLastQTR = Put(intnx('month',&Today.,-1,'end'),mmddyy10.);                         FirstCalDayofLastYear = Put(intnx('month',&Today.,-13),mmddyy10.);                         firstweek_count = Week(firstCalDayOfMonth);                         Call symputx("firstweek_count", firstweek_count);                         Call symputx("firstCalDayOfQTR_Date9", firstCalDayOfQTR_Date9);                         Call symputx("LastCalDayofLastQTR", LastCalDayofLastQTR);                         Call symputx("FirstCalDayofLastYear", FirstCalDayofLastYear);                     %end;                    %If %eval(&Currentmonth. in 5 8 11 ) %Then %Do;                         firstCalDayOfPrevMonth = intnx('month',&Today.,-1);                         firstCalDayOfQTR_Date9 = Put(firstCalDayOfPrevMonth, Date9.);                         LastCalDayofLastQTR = Put(intnx('month',&Today.,-2,'end'),mmddyy10.);                         FirstCalDayofLastYear = Put(intnx('month',&Today.,-14),mmddyy10.);                         firstweek_count = Week(firstCalDayOfPrevMonth);                         Call symputx("firstweek_count",firstweek_count);                         Call symputx("firstCalDayOfQTR_Date9",firstCalDayOfQTR_Date9);                         Call symputx("LastCalDayofLastQTR", LastCalDayofLastQTR);                         Call symputx("FirstCalDayofLastYear", FirstCalDayofLastYear);                     %end;                    %If %eval(&Currentmonth. in 6 9 12 ) %Then %Do;                         firstCalDayOfPrev2Month = intnx('month',&Today.,-2);                         firstCalDayOfQTR_Date9 = Put(firstCalDayOfPrev2Month, Date9.);                         LastCalDayofLastQTR = Put(intnx('month',&Today.,-3,'end'),mmddyy10.);                         FirstCalDayofLastYear = Put(intnx('month',&Today.,-15),mmddyy10.);                         firstweek_count = Week(firstCalDayOfPrev2Month);                         Call symputx("firstweek_count",firstweek_count);                         Call symputx("firstCalDayOfQTR_Date9",firstCalDayOfQTR_Date9);                         Call symputx("LastCalDayofLastQTR", LastCalDayofLastQTR);                         Call symputx("FirstCalDayofLastYear", FirstCalDayofLastYear);                     %end;             %Put &firstweek_count. &LastCalDayofLastQTR. &FirstCalDayofLastYear. &firstCalDayOfQTR_Date9.;             %Do %while ( &i. LE &Currentweek.);                 %If &i. GE &firstweek_count. %then %Do;                     %Let nextweek_count = %eval(&i.- &firstweek_count. + 1);                     %If &i. ne &Currentweek. %Then %Do;                         var3 = "week&nextweek_count._itemfile";                         var4 = "week&nextweek_count._vsi";                         Output;                     %end;                     %else %Do;                         new_var1 = "week&nextweek_count._itemfile";                         new_var2 = "week&nextweek_count._vsi";                         weekly_count1 = Left(Trimn(%eval(&nextweek_count.-1)));                         weekly_count2 = Left(Trimn(&nextweek_count.));                         Output;                     %end;                 %end;                 %Let i = %eval(&i.+1);             %end;         %end;     Run; %Mend Create_weekly_varlist; %Create_weekly_varlist;   Whereas when i was running the above code, the sas application has got hanged (showing "datastep is running" on top left side of application) and so I had to brake the session. The problem here is the Macro varibales(&firstweek_count. &LastCalDayofLastQTR. &FirstCalDayofLastYear. &firstCalDayOfQTR_Date9.) are not being resolved with in the %IF CONDITION.( %else %If &CurrentQTR. GT 1 %Then %Do;)???      Here, I need to get the MACRO Variables - &firstweek_count. &LastCalDayofLastQTR. &FirstCalDayofLastYear. &firstCalDayOfQTR_Date9  to be resolved with in the %ELSE %IF condition??? Can you please advice me on this??? Thanks, RAVISPR. ... View more

Thanks Amir. Now the above error has been overcome. ... View more

Hi PG, Hope u doing good.. I have been using the above concept of Libname-excel for updating the data in excel files. But, i have got an issue now. It couldnt able to populate the formats of the dataset which contains in SAS into excel-defined named range...!! and zeros too..which were showing in the sas dataset. Bt couldnt able to populate to excel sometimes. For EX:: the dataset contains as below. name  sal xxx     $4300.98 yyy     $2300.00 sss     0 ddd     $1900.00 ggg     0 If I use Libname - Datarange concept to populate the above data into an excel file, the output excel file is shown as below. name     sal xxx     4300 yyy     2300 sss     ddd     1900 ggg     which is missing all the required formats. I tried by pre-defining all these formats in excel dataranges. But no use. And missing these zeros here is very strange for me. Can you please help me in this..how to get exact formats into the defined Excel DataRanges..?? Many Thanks, SPR. ... View more

Hi Keith, Much Thanks for the reply. yes. currently I am using this Named Range concept for the reports where the data is replacing every day by creating Named ranges in excel file.. But, I also requires updating data to Right side columns instead of fixed columns. Please see the above Template of my excel file. Anyways, i will try to use VBA macros whcih can also be run from sas. Thanks, SPR. ... View more

Hi, Please find below for the format of the file. Every month i need to update the monthly data in the next columns. I can create Data ranges in excel before running sas code. But like this, it requires to create for the next 20 or 30 columns=20 or 30 Data Ranges for every month in excel file which is tedious. so, i am thinking to create these DataRanges through SAS if possible?? Delinq Nov-12 Dec-12 Jan-13 Feb-13 0 cycle Volume 321 566 378 Value 342452 675756 56345 1 cycle Volume 234 544 543 Value 454656 657754 8798678 2 cycle Volume 12 676 45 Value 43535 656565 565443 3 cycle Volume 34 89 456 Value 4536 7545 765756 4 cycle Volume 234 546 786 Value 675747 5444555 43534 5 cycle Volume 324 987 454 Value 4323425 546564 435643 Thanks, SPR. ... View more

But your code writes the data by checking condition with the excel cell values. Actually, i want to write/export data which is coming from sas datasteps. so, i cant check the conditions with the values presented in the excel. i.e its entirely new data for the new month which should be written to excel excel cell ranges. The problem here is unwanted column ranges..But for time being i am hiding those columns in the template file. Thanks, SPR. ... View more

Sorry Ksharp. if we use proc export we cant write data to required cell ranges in the excel...!! right?? is any further add-ons for my code which i mentioned above.i.e Libname and Proc SQL connect to excel, to eliminate column names while writing data into the cell ranges?? Thanks a lot. ... View more

Thanks a lot... yes. its correct. i got a way to write data to specific excel cells.But am getting variable names into the xl file those i dont want. below is the code. Here namedrng1 is the Named range in the excel file contains specific cells in a sheet. Libname xl excel "Path"; Data xl.namedrng1; set work.emp; run; here am getting columns names into xl file..is there any way to remove sas dataset's(work.emp) column names? I can hide these columns in the excel file...BUT....still....???? Regards, SPR. ... View more

Thank-You... i think we need to use Named range also in the excel files. ... View more