Help using Base SAS procedures

Identify first and last

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

Identify first and last

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


Accepted Solutions
Solution
‎11-15-2014 04:29 AM
Super User
Posts: 10,023

Re: Identify first and last

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


All Replies
Super User
Posts: 19,780

Re: Identify first and last

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;

Frequent Contributor
Posts: 98

Re: Identify first and last

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!

Solution
‎11-15-2014 04:29 AM
Super User
Posts: 10,023

Re: Identify first and last

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

Frequent Contributor
Posts: 98

Re: Identify first and last

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

Lizi

Frequent Contributor
Posts: 98

Re: Identify first and last

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!

Super User
Posts: 10,023

Re: Identify first and last

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

Frequent Contributor
Posts: 98

Re: Identify first and last

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!

Super User
Posts: 10,023

Re: Identify first and last

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

Frequent Contributor
Posts: 98

Re: Identify first and last

Thanks a lot, Keshan. You helped me tons.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 395 views
  • 6 likes
  • 3 in conversation