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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.