topic Re: Do Until loop in macro environnement not compiling in SAS Programming

Do Until loop in macro environnement not compiling

ingridf — Tue, 31 Oct 2017 09:07:27 GMT

Hi,

I'm having an issue using the do until statement in a macro, here is a watered down version of what I'm trying to achieve :

data test;
input id date_effet mois_resil;
cards;
1 201704 201804
2 201705 201712
;
run;

%Macro essai;

data result;
set test;
%let month = 201701;
%do %until (&month. = mois_resil) ;
	if &month. = date_effet then do;
		nb_j_&month. = 29;
		%let i = %eval(&month. + 1);
		%do %until (&i. = mois_resil) ;
			nb_j_&i. = 31;
			%let i = %eval(&i. + 1);
    		%if %substr(&i., 5, 2) = 12 %then %let i = %eval(&i. + 88);
		%end;
	end;
	%let month = %eval(&month. + 1);
    %if %substr(&month., 5, 2) = 12 %then %let month = %eval(&month. + 88);       
%end;
nb_j_&month. = 28;

run;

%mend;

%essai;

I don't understand why it's not compiling, thanks in advance

Re: Do Until loop in macro environnement not compiling

gamotte — Tue, 31 Oct 2017 09:15:27 GMT

Hello,

%do %until (&month. = mois_resil) ;

Here, you are comparing the value of macrovariable month witjh the word mois_resil.

This is a regular loop that you need here, not a macro loop.

Re: Do Until loop in macro environnement not compiling

RW9 — Tue, 31 Oct 2017 09:18:01 GMT

Its because you are mixing Base SAS and Macro. Let me clarify macro is not a replacement for Base SAS. Macro only creates text.

The macro part of your code is replaced before any code is run by the macro pre-processor and the Base SAS code which is generated is then compiled and run. There is, from the code you post, no need or reason to use macro in this at all, simple Base SAS datastep can achieve what you want. If you provide what you want the output to be then I will show you.

Re: Do Until loop in macro environnement not compiling

Kurt_Bremser — Tue, 31 Oct 2017 09:23:48 GMT

Classic misunderstanding of the macro PREprocessor.

The preprocessor only sees the text at the time it executes (while the code is being fetched and BEFORE any steps are compiled and executed!), so it NEVER has access to data step variables.

Since macro only knows the datatype text, the condtions look like the following after resolution of macro variables:

%do %until (201701 = mois_resil)

which will never be true, and

%let i = %eval(201701 + 1);
%do %until (201702 = mois_resil) ;

which will also never be true, resulting in endless macro loops.

What are you trying to achieve, anyway? Please post an example for the desired result. As in 99% of these cases, you probably won't need a macro at all.

Re: Do Until loop in macro environnement not compiling

ingridf — Tue, 31 Oct 2017 09:27:09 GMT

Thank you for your answers. The variable "nb_j_&month." is the reason why I'm using macros. I need as many as variables as there are months between "date_effet" (subscription date) and "mois_resil" (resiliation date), with that syntax (nb_j_+ month in question). Is there another way to achieve this without using macros?

And basically I want to assign a particular value for the first month, another value for the last month and the same value for all the months in between.

Thanks again.

Re: Do Until loop in macro environnement not compiling

Kurt_Bremser — Tue, 31 Oct 2017 09:37:34 GMT

Please post the desired result out of your example dataset.

Do you want to create multiple variables? If yes, you need to know the range of variables before you execute the macro do loop. You CANNOT make it dependent on datastep values. To do that, you need to evaluate possible values first in a separate step, so you can then control the action of the macro.

But you might have a task that is best solved with proc transpose. Once again, post the desired result in tabular form.

Re: Do Until loop in macro environnement not compiling

RW9 — Tue, 31 Oct 2017 09:41:30 GMT

Please show a clear example, provide input dataset and required output. Its not clear from the post. From a guess it sounds like arrays would be what you want:

data _null_;
  set test;
  call symput('mb',intck('month',date_effet,mois_resil));
run;

data want;
  set have;
  array v{&mb.};
  ...
run;

However I caution the use of columns for data, its is generally far easier to work with data in rows rather than columns.

Re: Do Until loop in macro environnement not compiling

ingridf — Tue, 31 Oct 2017 09:52:46 GMT

Using this simple data :

data test;
input id date_effet mois_resil;
cards;
1 201701 201801
2 201702 201711
;
run;

I just realized I forgot to initialize my variable, here is the revised code :

%Macro essai;

data result;
set test;

%do i = 201701 %to 201801;
	nb_j_&i. = 0;
    %if %substr(&month., 5, 2) = 12 %then %let month = %eval(&month. + 88);       
%end;

%let month = 201701;
do until (&month. = mois_resil) ;
	if &month. = date_effet then do;
		nb_j_&month. = 29;
		%let i = %eval(&month. + 1);
		do until (&i. = mois_resil) ;
			nb_j_&i. = 31;
			%let i = %eval(&i. + 1);
    		%if %substr(&i., 5, 2) = 12 %then %let i = %eval(&i. + 88);
		end;
	end;
	%let month = %eval(&month. + 1);
    %if %substr(&month., 5, 2) = 12 %then %let month = %eval(&month. + 88);       
end;
nb_j_&month. = 28;

run;

%mend;

%essai;

This is the result I want :

id      date_effet    mois_resil   nb_j_201701  nb_j_201702 ....... nb_j_201801
1       201701        201801       29                 31                    28              
2       201702        201711       0                  29                     0

My actual dataset is of course much longer

Re: Do Until loop in macro environnement not compiling

Kurt_Bremser — Tue, 31 Oct 2017 10:04:47 GMT

You would have to retrieve the smallest value for data_effet and the largest value for mois_resil first, so you know the upper and lower bounds of your array first.

But producing such a dataset is not effective anyway, instead store such data in a long format:

First of all, store dates as SAS dates:

data test;
input id date_effet :yymmdd8. mois_resil :yymmdd8.;
format date_effet mois_resil yymmddn8.;
cards;
1 20170101 20180101
2 20170201 20171101
;
run;

Then create a vertical dataset with all periods for your ID's:

data result;
set test;
format period yymmddn8.;
period = date_effet;
do while period le mois_resil;
  output;
  period = intnx('month',period,1,'b');
end;
drop date_effet mois_resil;
run;

and work with that. The wide format is unwieldy and wastes space.

Re: Do Until loop in macro environnement not compiling

ingridf — Tue, 31 Oct 2017 10:22:05 GMT

The reason why I'm working with "year&month" syntax for my variables (date_effet and mois_resil) is because that's the increment I want for my "nb_j_" variable. Using the full format ("year&month&date") would give me way too many "nb_j_" variables.

Also, my actual data has a little over 1M lines and the time period would go from 201110 (smallest value of date_effet) to 202103 (largest value of mois_resil), wouldn't creating a vertical dataset with all periods for each ID take up way too much space?

Re: Do Until loop in macro environnement not compiling

Kurt_Bremser — Tue, 31 Oct 2017 11:51:04 GMT

The year_month syntax in the form you used is bogus, because incrementing 201612 will give you 201613 and not 201701. You will have to use an extra if condition to handle that; using proper SAS dates and the intnx() function takes care of it all, while the display format yymmn6. would remove the display of the days. And this also enables all the other date-related functions, most importantly intck().

Just because you have so many records, the long format would save space.

You have a proposed maximum range of 10*12-7 months, and I seriously doubt that many of your original observations would span over the whole range or at least a significant part of it. That means that most of your values in the wide format would be either zero or missing. The long format prevents writing a record for such instances in the first place, and will reduce the overall dataset size. Combined with the fact that you then have a uniform structure with fixed simple variable names, programming will become easier/simpler by orders of magnitude.

Re: Do Until loop in macro environnement not compiling

gamotte — Tue, 31 Oct 2017 14:16:50 GMT

Hello,

Assuming your data is stored as sas dates, otherwise, you will need a preliminary conversion :

data test;
informat date_effet mois_resil yymmn6.;
format date_effet mois_resil yymmn6.;
input id date_effet mois_resil;
cards;
1 201701 201801
2 201702 201711
;
run;

/* 1. Determination of the min and max dates and the length necessary for generating a string */
/*    that concatenates all column names m<min_date> -- m<max_date>                           */
proc sql noprint;
	SELECT min(min(date_effet,mois_resil)) AS min_date, max(max(date_effet,mois_resil)) AS max_date, 
	       12*(intck("month",CALCULATED min_date, CALCULATED max_date)+1)+1
	INTO :min_date, :max_date, :strlength
	FROM test;
quit;

/* 2. Generation of column names */
data _NULL_;
	length dates $&strlength.;

	m=&min_date.;

	dates=cats("nb_j_",put(m,yymmn6.));
	i=0;
	do while (m<&max_date.);
		m=intnx("month",m,1);
	    dates=cat(strip(dates)," nb_j_", put(m,yymmn6.));
		i=i+1;
	end;
	call symput("dates",dates);
run;

%put &=dates.;

/* 3. Generation of the desired output */
data result;
	set test;

	format &dates. 3.;

	array nb_j(*) &dates.;

	effet=0;

	do i=1 to dim(nb_j);
		if effet then nb_j(i)=31;
		if substr(vname(nb_j(i)),6)=put(date_effet,yymmn6.) then do;
			effet=1;
			nb_j(i)=29;
		end;
		if substr(vname(nb_j(i)),6)=put(mois_resil,yymmn6.) then leave;
	end;

	nb_j(i-1)=28;

	drop effet i;

run;

Re: Do Until loop in macro environnement not compiling

ingridf — Fri, 03 Nov 2017 08:57:51 GMT

Thank you all for your help, I used @gamotte 's solution & tweaked it to fit my data