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
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;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: