BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ingridf
Calcite | Level 5

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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;

View solution in original post

12 REPLIES 12
gamotte
Rhodochrosite | Level 12

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Kurt_Bremser
Super User

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.

ingridf
Calcite | Level 5

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.

 

 

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ingridf
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

ingridf
Calcite | Level 5

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?

 

Kurt_Bremser
Super User

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.

gamotte
Rhodochrosite | Level 12

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;
ingridf
Calcite | Level 5

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

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1459 views
  • 3 likes
  • 4 in conversation