Help Needed ASAP - Writing Do loop with dynamic variables

[Kuda35]

I am trying to run a nested  do loop  that creates  columns "EXP_&i&j" and sets the appropriate values. Currently using the following code below.

Unfortunately not seeing any values coming through for i,j,mmdate,eom,numdays,x.  Not sure what the issue is. Please help

%Do i = 2010 %to 2010;
%Do j = 1 %to 2;
k = 1;

mmdate=mdy(j,k,i);
format mmdate ddmmyy10.;

enddate = TO_DATE;
eom=intnx( 'month', mmdate,0,'end');
numdays=day(eom);

x = 0;

if mmdate>= start_date and mmdate<= enddate then x = numdays;
if year(mmdate)= year(start_date) and month(mmdate)= month(start_date) then x = numdays - day(start_date);
if year(mmdate)= year(enddate) and month(mmdate)= month(enddate) then x = day(enddate);

EXP_&i&j = x;

output;
%End;
%End;
%mend A;
%A;
run;

[Reeza]

Why do you need help ASAP? 

Your mixing macro and data step logic. Use one or the other. 

For example you never use your i/j macro variables.  

It also appears as if you've posted a portion of your code. 

[PGStats]

Use &i and &j everywhere to refer to the macro variables created by your %do loops.

It is not clear what you are trying to do. But I suspect that it doesn't require macro coding at all. Take a step back and describe the problem you are trying to solve.

[BrunoSilva]

 Hello,

As refered before by @Reeza your are mixing macro and data step logic.

But even without knowing a full picture of what you entend to do here some code based on your sample:

Some assumption:

1. I don't have To_Date var so i used : To_Date=today();
2. I don't know what you wan't as final result so the table Stuff is just also one asumption

data Some;

	format mmdate ddmmyy10.;
	length varname $10 ;
	TO_DATE=today();

	Do i = 2010 to 2010;
		Do j = 1 to 2;
			k = 1;
			mmdate=mdy(j,k,i);
			
			enddate = TO_DATE;
			eom=intnx( 'month', mmdate,0,'end');
			numdays=day(eom);
			x = 0;

			if mmdate>= start_date and mmdate<= enddate then
				x = numdays;

			if year(mmdate)= year(start_date) and month(mmdate)= month(start_date) then
				x = numdays - day(start_date);

			if year(mmdate)= year(enddate) and month(mmdate)= month(enddate) then
				x = day(enddate);
			VALUE = x;

                        /* will be the future column name */
			VARNAME=CATT("EXP_",i,strip(put(j,z2.)));
			output;
		End;
	End;
run;

/* using transpose to create the columns as defined on the varname variable */
proc transpose data=some
	out=stuff (drop=_:)	name=_source;
	id varname;
	var value;
run; quit;

 Best regards.

[Kuda35]

Hi all, thanks for you replies so far. 

To clarify, I have a table with data  membership on customers with From_date(start) and to_date(end), which can be over several years. I need to calculate : .

-  the member of days in each month that the person is a member.

To do this, I will would like to add a new column for each month 'EXPMMYYYY' to the dataset  and calculatte the membership days for each month. This code will form part of a process  that will be repeated on a monthly basis

Becuase of this, I need  the looping code that allows me to add  the 'EXPMMYYYY' columns dynamically to the dataset.

I have tried the solutions suggested but that haven't quite work.  Apologies if I am missing something. I am new to SAS.  

[Reeza]

1. Post your full code

2. Post sample data and expected output

[Kuda35]

Please find full code  and input and output data attached. 

Libname worklib '/mnt/silverbhw/Common_Share/Temp/IDM/Work/';

data worklib.ExposureC;
      set worklib.ExposureB;

/* Exposure months*/

     format mmdate ddmmyy10.;

     Do i = 2010 to 2010;
     Do j = 1 to 2;
     k = 1;

     mmdate=mdy(j,k,i);
     output;

start_date = FROM_DATE;
enddate = TO_DATE;
eom=intnx( 'month', mmdate,0,'end');
numdays=day(eom);
output;

x = 0;

if mmdate>= start_date and mmdate<= enddate then
x = numdays;

if year(mmdate)= year(start_date) and month(mmdate)= month(start_date) then
x = numdays - day(start_date);

if year(mmdate)= year(enddate) and month(mmdate)= month(enddate) then
x = day(enddate);
Varname = x;
output;

End;
End;
run;

[Kuda35]

Sorry, please see revised input/output file attached.

[ballardw]

---

@Kuda35 wrote:

Hi all, thanks for you replies so far. 

To do this, I will would like to add a new column for each month 'EXPMMYYYY' to the dataset  and calculatte the membership days for each month. This code will form part of a process  that will be repeated on a monthly basis

You are likely better off having one record per start and one with end with a value to tell you which month it is and use SAS date values

Adding new columns every month means that your data processing just gets more complicated every time you add a column(variable).