BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lizzy28
Quartz | Level 8

Hi all,

I have to admit my do-loop skill is too weak. I need to sort out the first and last months when shipping was made for each year within a year. As shown below, the columns of startmon and endmon are my objective variables I want.

OrderIDmonsmon1mon2mon3mon4mon5mon6mon7mon8mon9mon10mon11mon12yearstartmonendmon
14111100000000201214
2110111111111112012212
3121111111111112012112
41100000000000201211
56000111111000201249

Also, for the orders across years as below, I need to abstract a separate file with startmon, endmon, startyr and endyr.

Original data:

OrderIDmonsmon1mon2mon3mon4mon5mon6mon7mon8mon9mon10mon11mon12year
11212111111111112011
330000000001112011
4110111111111112011
141111000000002012
2110111111111112012
3121111111111112012
411000000000002012
560001111110002012
1121111111111112013
2101111111111002013
321100000000002013
5121111111111112013

Objective data:

OrderIDstartmonendmonstaryrendyr
11420112012
221020122013
310220112013
42120112012
541220122013

Your ideas are greatly appreciated!

Lizi

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

I want know if there are some non-consecutive range ,like :

data have;

input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year startmon endmon;

cards;

1 4 1 1 1 1 0 0 0 0 1 1 1 1 2012 1 4

2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 2 12

3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 1 12

4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 1 1

5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 4 9

;

For the first one:

data have;
input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year startmon endmon;
cards;
1 4 1 1 1 1 0 0 0 0 0 0 0 0 2012 1 4
2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 2 12
3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 1 12
4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 1 1
5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 4 9
;
run;
data temp;
 set have;
 array m{*} mon1-mon12;
 do i=1 to dim(m);
  value=m{i};output;
 end;
 keep  OrderID i value;
run;
data want;
 set temp;
 by OrderID value notsorted;
 retain start_mon ;
 if first.value then start_mon=i;
 if last.value and value=1 then do;end_mon=i;output;end;
 drop i value;
run;

For the second one:


data have;
input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year ;
cards;
1     12     1     1     1     1     1     1     1     1     1     1     1     1     2011
3     3     0     0     0     0     0     0     0     0     0     1     1     1     2011
4     11     0     1     1     1     1     1     1     1     1     1     1     1     2011
1     4     1     1     1     1     0     0     0     0     0     0     0     0     2012
2     11     0     1     1     1     1     1     1     1     1     1     1     1     2012
3     12     1     1     1     1     1     1     1     1     1     1     1     1     2012
4     1     1     0     0     0     0     0     0     0     0     0     0     0     2012
5     6     0     0     0     1     1     1     1     1     1     0     0     0     2012
1     12     1     1     1     1     1     1     1     1     1     1     1     1     2013
2     10     1     1     1     1     1     1     1     1     1     1     0     0     2013
3     2     1     1     0     0     0     0     0     0     0     0     0     0     2013
5     12     1     1     1     1     1     1     1     1     1     1     1     1     2013
;
run;
data temp;
 set have;
 array m{*} mon1-mon12;
 do i=1 to dim(m);
  value=m{i};date=mdy(i,1,year);output;
 end;
 format date date9.;
 keep  OrderID date  value;
run;
proc sort data=temp; by OrderID date;run;
data want;
 set temp;
 by OrderID   value notsorted;
 retain start_mon start_year;
 if first.value then do;start_mon=month(date); start_year=year(date); end;
 if last.value and value=1 then do;end_mon=month(date); end_year=year(date);output;end;
 drop value;
run;

Xia Keshan

View solution in original post

9 REPLIES 9
Reeza
Super User

Here's the first part, first/last month. I'm not sure about the logic of first/last year? You just want the first/last year per order id? Which start/end month should align with the years?

data have;

input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year startmon endmon;

cards;

1 4 1 1 1 1 0 0 0 0 0 0 0 0 2012 1 4

2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 2 12

3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 1 12

4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 1 1

5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 4 9

;

run;

data want;

set have;

length string $12.;

array mnth(12) mon1-mon12;

string=cats(of mon1-mon12);

first_month=find(string, '1');

last_month=12-find(reverse(string), '1')+1;

run;

lizzy28
Quartz | Level 8

Thanks a lot, Reeza.

You addressed my first question.

For the second part, I need to observe the months across several years and find the first and last months over the years as well as the first and last years of shipping per order. I think I can use your idea once I transform the data from long to wide. Thanks again!

Ksharp
Super User

I want know if there are some non-consecutive range ,like :

data have;

input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year startmon endmon;

cards;

1 4 1 1 1 1 0 0 0 0 1 1 1 1 2012 1 4

2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 2 12

3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 1 12

4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 1 1

5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 4 9

;

For the first one:

data have;
input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year startmon endmon;
cards;
1 4 1 1 1 1 0 0 0 0 0 0 0 0 2012 1 4
2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 2 12
3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 1 12
4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 1 1
5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 4 9
;
run;
data temp;
 set have;
 array m{*} mon1-mon12;
 do i=1 to dim(m);
  value=m{i};output;
 end;
 keep  OrderID i value;
run;
data want;
 set temp;
 by OrderID value notsorted;
 retain start_mon ;
 if first.value then start_mon=i;
 if last.value and value=1 then do;end_mon=i;output;end;
 drop i value;
run;

For the second one:


data have;
input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year ;
cards;
1     12     1     1     1     1     1     1     1     1     1     1     1     1     2011
3     3     0     0     0     0     0     0     0     0     0     1     1     1     2011
4     11     0     1     1     1     1     1     1     1     1     1     1     1     2011
1     4     1     1     1     1     0     0     0     0     0     0     0     0     2012
2     11     0     1     1     1     1     1     1     1     1     1     1     1     2012
3     12     1     1     1     1     1     1     1     1     1     1     1     1     2012
4     1     1     0     0     0     0     0     0     0     0     0     0     0     2012
5     6     0     0     0     1     1     1     1     1     1     0     0     0     2012
1     12     1     1     1     1     1     1     1     1     1     1     1     1     2013
2     10     1     1     1     1     1     1     1     1     1     1     0     0     2013
3     2     1     1     0     0     0     0     0     0     0     0     0     0     2013
5     12     1     1     1     1     1     1     1     1     1     1     1     1     2013
;
run;
data temp;
 set have;
 array m{*} mon1-mon12;
 do i=1 to dim(m);
  value=m{i};date=mdy(i,1,year);output;
 end;
 format date date9.;
 keep  OrderID date  value;
run;
proc sort data=temp; by OrderID date;run;
data want;
 set temp;
 by OrderID   value notsorted;
 retain start_mon start_year;
 if first.value then do;start_mon=month(date); start_year=year(date); end;
 if last.value and value=1 then do;end_mon=month(date); end_year=year(date);output;end;
 drop value;
run;

Xia Keshan

lizzy28
Quartz | Level 8

Thanks so much, Keshan! You have what I've been looking for.

Lizi

lizzy28
Quartz | Level 8

Keshan, I didn't pay attention to your comments. But yes, there can be some nonconsecutive ranges. If I only care about the start month and the end month only despite non-consecutive ranges, how would the program change? Thanks again!

Ksharp
Super User

You mean consecutive range's start month and end month?  If it was, keep my code intact .

lizzy28
Quartz | Level 8

I mean there can be breaks during a year. Just like OrderID#6 as below:

OrderIDmonsmon1mon2mon3mon4mon5mon6mon7mon8mon9mon10mon11mon12year
11212111111111112011
330000000001112011
4110111111111112011
651100000001112011
141111000000002012
2110111111111112012
3121111111111112012
411000000000002012
560001111110002012
6111111111111102012
1121111111111112013
2101111111111002013
321100000000002013
5121111111111112013
680111111011002013

OrderID #6 has 4 breaks, but I only need the first start, month mon1 (January) of 2011, and the last end month, month mon10 (October) of 2013

OrderIDmonsmon1mon2mon3mon4mon5mon6mon7mon8mon9mon10mon11mon12year
651100000001112011
6111111111111102012
680111111011002013

So, the target data is

OrderIDstartmonstartyrendmonendyr
11201142012
222012102013
310201122013
42201112012
542012122013
612011102013

Thanks for all the help, Keshan!

Ksharp
Super User

OK. Here is .


data have;
input OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year ;
cards;
1     12     1     1     1     1     1     1     1     1     1     1     1     1     2011
3     3     0     0     0     0     0     0     0     0     0     1     1     1     2011
4     11     0     1     1     1     1     1     1     1     1     1     1     1     2011
6     5     1     1     0     0     0     0     0     0     0     1     1     1     2011
1     4     1     1     1     1     0     0     0     0     0     0     0     0     2012
2     11     0     1     1     1     1     1     1     1     1     1     1     1     2012
3     12     1     1     1     1     1     1     1     1     1     1     1     1     2012
4     1     1     0     0     0     0     0     0     0     0     0     0     0     2012
5     6     0     0     0     1     1     1     1     1     1     0     0     0     2012
6     11     1     1     1     1     1     1     1     1     1     1     1     0     2012
1     12     1     1     1     1     1     1     1     1     1     1     1     1     2013
2     10     1     1     1     1     1     1     1     1     1     1     0     0     2013
3     2     1     1     0     0     0     0     0     0     0     0     0     0     2013
5     12     1     1     1     1     1     1     1     1     1     1     1     1     2013
6     8     0     1     1     1     1     1     1     0     1     1     0     0     2013
;
run;
data temp(where=(value=1));
 set have;
 array m{*} mon1-mon12;
 do i=1 to dim(m);
  value=m{i};date=mdy(i,1,year);output;
 end;
 format date date9.;
 keep  OrderID date  value;
run;
proc sort data=temp; by OrderID date;run;
data want;
 set temp;
 by OrderID;
 retain start_mon start_year;
 if first.OrderID then do;start_mon=month(date); start_year=year(date); end;
 if last.OrderID then do;end_mon=month(date); end_year=year(date);output;end;
 drop value;
run;

Xia Keshan

lizzy28
Quartz | Level 8

Thanks a lot, Keshan. You helped me tons.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 1263 views
  • 6 likes
  • 3 in conversation