DATA Step, Macro, Functions and more

Help Needed ASAP - Writing Do loop with dynamic variables

Reply
Occasional Contributor
Posts: 9

Help Needed ASAP - Writing Do loop with dynamic variables

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;

 

 

Super User
Posts: 17,828

Re: Help Needed ASAP - Writing Do loop with dynamic variables

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. 

Respected Advisor
Posts: 4,646

Re: Help Needed ASAP - Writing Do loop with dynamic variables

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.

PG
Contributor
Posts: 50

Re: Help Needed ASAP - Writing Do loop with dynamic variables

[ Edited ]

 

 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 Smiley Happy

 

 

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.

Occasional Contributor
Posts: 9

Re: Help Needed ASAP - Writing Do loop with dynamic variables

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.  

 

Super User
Posts: 17,828

Re: Help Needed ASAP - Writing Do loop with dynamic variables

1. Post your full code

2. Post sample data and expected output

Occasional Contributor
Posts: 9

Re: Help Needed ASAP - Writing Do loop with dynamic variables

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;

Occasional Contributor
Posts: 9

Re: Help Needed ASAP - Writing Do loop with dynamic variables

Sorry, please see revised input/output file attached.

Super User
Posts: 10,500

Re: Help Needed ASAP - Writing Do loop with dynamic variables


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).

 

Ask a Question
Discussion stats
  • 8 replies
  • 532 views
  • 0 likes
  • 5 in conversation