topic Re: Help Needed ASAP - Writing Do loop with dynamic variables in SAS Programming

Help Needed ASAP - Writing Do loop with dynamic variables

Kuda35 — Mon, 02 May 2016 22:52:45 GMT

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;

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

Reeza — Mon, 02 May 2016 23:58:13 GMT

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.

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

PGStats — Tue, 03 May 2016 02:41:55 GMT

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.

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

BrunoSilva — Tue, 03 May 2016 11:00:47 GMT

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:

I don't have To_Date var so i used : To_Date=today();
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.

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

Kuda35 — Tue, 03 May 2016 14:16:29 GMT

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.

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

Reeza — Tue, 03 May 2016 14:24:50 GMT

1. Post your full code

2. Post sample data and expected output

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

ballardw — Tue, 03 May 2016 14:37:53 GMT

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

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

Kuda35 — Tue, 03 May 2016 15:10:05 GMT

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;

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

Kuda35 — Tue, 03 May 2016 15:11:46 GMT

Sorry, please see revised input/output file attached.