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