Data Step Help!:How-to: Create array Do Loops (date columns) and conditionally populate columns from existing data set

tsengj — Thu, 23 Feb 2012 05:24:11 GMT

Hi,

What is the best way to create new columns (by month) using arrays and conditionally populate these columns from an existing data set when data meets the date (newly created columns) criteria?

For example, see source data set below:

City | Volume | Start Date | End Date
A     10          2/10/2010    1/11/2010
A     20          2/11/2010     3/12/2010
A     15          5/1/2011      6/2/2011
B     50          7/2/2011      9/3/2011

How do I create table below from the above?

City | Volume | Start Date | End Date | 1-Oct-10 | 1-Nov-10 | 1-Dec-10 | 1-Jan-11 | 1-Feb-11 | 1-Mar-11
A     10          2/10/2010    1/11/2010                    10
A     20          2/11/2010     3/12/2010                                    20
A     15          5/1/2011      6/2/2011                                                       15
B     50          7/2/2011      9/3/2011                                                                                      50

I suspect i may need Do Loop Arrays, but can they be as date and how should i use conditions on column names? Any Ideas?

Thank you in advance!

jimmy

Data Step Help!:How-to: Create array Do Loops (date columns) and conditionally populate columns from existing data set

Patrick — Thu, 23 Feb 2012 06:04:21 GMT

Below code will create a data structure close to what you asked for. The main difference is that a new month_var will only be created if there is also a corresponding start_date in the data.

data have;
input City $ Volume Start_Date:ddmmyy. End_Date:ddmmyy.;
format Start_Date End_Date ddmmyy10.;
Start_Date_BeginMonth=put(intnx('month',Start_Date,0,'b'),date11.);
datalines;
A 10 2/10/2010 1/11/2010
A 20 2/11/2010 3/12/2010
A 15 5/1/2011 6/2/2011
B 50 7/2/2011 9/3/2011
;
run;

proc transpose data=have out=want(drop=_name_);
by city volume start_date end_date notsorted;
id Start_Date_BeginMonth;
var volume;
run;

proc print data=want;
run;

topic Data Step Help!:How-to: Create array Do Loops (date columns) and conditionally populate columns from existing data set in SAS Programming

Data Step Help!:How-to: Create array Do Loops (date columns) and conditionally populate columns from existing data set

Data Step Help!:How-to: Create array Do Loops (date columns) and conditionally populate columns from existing data set