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;
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.
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.
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:
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.
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.
1. Post your full code
2. Post sample data and expected output
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;
Sorry, please see revised input/output file attached.
@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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.