BookmarkSubscribeRSS Feed
tsengj
Calcite | Level 5

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

1 REPLY 1
Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 664 views
  • 0 likes
  • 2 in conversation