topic Re: Generating Reiterated Months and Years with Do Loop in SAS Programming

Generating Reiterated Months and Years with Do Loop

dbcrow — Tue, 25 Jun 2019 23:37:48 GMT

Hi, SAS Community-

This shouldn't be difficult, but I'm having a rough go of it. I'm trying to generate a file in which each of the 58 California counties has 108 data points that are monthly increments (12) within years for nine (9) years of data, 2010-2018 (9 x 12 = 108). The way I'm setting it up, the file has four columns: county, date, year, and month. So, for the first county, Alameda, we would have:

County Date Year Month

Alameda 01/01/2010 2010 1

Alameda 02/01/2010 2010 2

Alameda 12/01/2018 2018 12

The problem with the code that follows is that when I get to the end of the first county, Alameda, instead of the date counter starting over at 01/01/2010 in the next county (Alpine) the date continues.

What I want is this:

Alpine 01/01/2010 2010 1

What I get is this:

Alpine 01/01/2019 2019 1

Here's the code:

data template(keep=county yearmonth month year);
	retain county yearmonth month year;
	yearmonth = "01dec2009"d;
	do i = 1 to n;
		do year = 2010 to 2018;
			do month = 1 to 12;
				yearmonth = intnx("month",yearmonth,1); 
				set counties nobs=n point=i;
			output;
			end;
		end;
	end;
stop;
format yearmonth MMDDYYS.;
run;

Note that the data set "counties" is simply a list of the 58 counties.

Any pointers would be greatly appreciated.

Regards,

David

Re: Generating Reiterated Months and Years with Do Loop

Tom — Tue, 25 Jun 2019 23:45:50 GMT

Put the DO loop(s) after the SET statement. So you read in one county and output multiple observations for that county.

%let start='01JAN2010'd;
%let end='01DEC2018'd;
data template(keep=county yearmonth month year);
  set counties (keep=county);
  do i=0 to intck('month',&start,&end);
     yearmonth=intnx('month',&start,i);
     year=year(yearmonth);
     month=month(yearmonth);
    output;
  end;
  format yearmonth MMDDYYS10.;
run;

Re: Generating Reiterated Months and Years with Do Loop

Astounding — Tue, 25 Jun 2019 23:51:39 GMT

An easy formula to replace the current one:

yearmonth = mdy(month, 1, year);

It would be faster to execute the SET statement sequentially:

data template(keep=county yearmonth month year);
   set counties;
		do year = 2010 to 2018;
			do month = 1 to 12;
				yearmonth = mdy(month, 1, year);
			output;
			end;
		end;
	end;
stop;
format yearmonth MMDDYYS.;
run;

The order of the observations would change, however. It's not clear which order would be best, but you can always sort the data.

Re: Generating Reiterated Months and Years with Do Loop

dbcrow — Tue, 25 Jun 2019 23:55:56 GMT

Many thanks! Worked like a charm.

Re: Generating Reiterated Months and Years with Do Loop

dbcrow — Wed, 26 Jun 2019 00:00:11 GMT

This is also a great solution. I got it a few seconds too late to accept it as a solution, unfortunately. (The SAS Users Community really should allow someone to accept more than one solution.) Many thanks as well.

Re: Generating Reiterated Months and Years with Do Loop

ballardw — Wed, 26 Jun 2019 15:35:46 GMT

@dbcrow wrote:

This is also a great solution. I got it a few seconds too late to accept it as a solution, unfortunately. (The SAS Users Community really should allow someone to accept more than one solution.) Many thanks as well.

Don't worry about marking only a "first" solution as correct. The main thing is that a solution is indicated so that others searching for forum for similar issues can tell that particular question was solved somehow.

Depending on the specific problem there are often 3 or, some times many, more "correct" solutions.