Hello everybody,
I have data as shown in the table below, wherein I need to create an indicator based on combined delivery of AC models.
Have table:
#. |
Year |
id |
AC model |
component |
date |
time1 |
time2 |
1 |
1989 |
aaa |
001 |
bb1 |
ccc |
abc |
xyz |
2 |
1989 |
aaa |
002 |
bb1 |
ccc |
abc |
xyz |
3 |
1989 |
aaa |
001 |
bb1 |
ccc |
efg |
hij |
4 |
1989 |
aaa |
002 |
bb1 |
ccc |
efg |
hij |
5 |
1989 |
aaa |
001 |
bb1 |
ooo |
fdc |
hkg |
6 |
1989 |
aaa |
002 |
bb1 |
ooo |
fdc |
hkg |
7 |
1989 |
aaa |
001 |
bb2 |
ddd |
lmn |
opq |
8 |
1989 |
aaa |
002 |
bb2 |
eee |
def |
ghi |
9 |
1989 |
aaa |
003 |
bb1 |
fff |
rst |
uvw |
10 |
1989 |
aaa |
004 |
bb1 |
fff |
rst |
uvw |
11 |
1990 |
bbb |
001 |
bb1 |
ggg |
def |
ghi |
12 |
1190 |
bbb |
002 |
bb1 |
ggg |
def |
ghi |
13 |
1990 |
bbb |
001 |
bb2 |
hhh |
jkl |
mno |
14 |
1990 |
bbb |
001 |
bb2 |
iii |
jkl |
mno |
15 |
1990 |
bbb |
002 |
bb2 |
kkk |
fgh |
ijk |
16 |
1990 |
bbb |
002 |
bb2 |
kkk |
fgh |
ijk |
17 |
1990 |
aaa |
003 |
bb1 |
jjj |
vwx |
cde |
18 |
1990 |
aaa |
004 |
bb1 |
jjj |
vwx |
cde |
19 |
1991 |
ccc |
001 |
bb1 |
lll |
bcd |
mno |
20 |
1991 |
ccc |
002 |
bb1 |
mmm |
qew |
pqr |
21 |
1991 |
ccc |
005 |
bb1 |
sdj |
djg |
jfk |
22 |
1991 |
ccc |
006 |
bb1 |
sdj |
djg |
jfk |
So basically, wherever an AC model with the same id and component = bb1 has been delivered on the same date and in the same time frame, there needs to be an indicator for a combined delivery. For AC model = 001 and 002, indicator = "A" (rows 1&2, 3&4, 5&6 and 11&12) For AC model = 003 and 004, indicator = "B" (rows 9&10 and 17&18). For AC model = 005 and 006, indicator = "C" (rows 21&22) and so on and so forth.
If an AC model with the same id and component = bb1 has different delivery dates/times, then the delivery is not combined and indicator = AC model (rows 19&20). Similarly, in cases where component = bb2, indicator = AC model (rows 7, 8, 13, 14, 15 & 16).
The delivery can be at different times on the same date (as seen in rows 1&2 and 3&4) or on different dates/times (rows 5&6). Also, the delivery combination is never random and always as pairs of 001 and 002, 003 and 004, 005 and 006, etc.
The row # and year are only for reference and not to be taken into consideration for the indicator. The table should look as below:
Want table:
#. |
Year |
id |
AC model |
component |
date |
time1 |
time2 |
Indicator |
1 |
1989 |
aaa |
001 |
bb1 |
ccc |
abc |
xyz |
A |
2 |
1989 |
aaa |
002 |
bb1 |
ccc |
abc |
xyz |
A |
3 |
1989 |
aaa |
001 |
bb1 |
ccc |
efg |
hij |
A |
4 |
1989 |
aaa |
002 |
bb1 |
ccc |
efg |
hij |
A |
5 |
1989 |
aaa |
001 |
bb1 |
ooo |
fdc |
hkg |
A |
6 |
1989 |
aaa |
002 |
bb1 |
ooo |
fdc |
hkg |
A |
7 |
1989 |
aaa |
001 |
bb2 |
ddd |
lmn |
opq |
001 |
8 |
1989 |
aaa |
002 |
bb2 |
eee |
def |
ghi |
002 |
9 |
1989 |
aaa |
003 |
bb1 |
fff |
rst |
uvw |
B |
10 |
1989 |
aaa |
004 |
bb1 |
fff |
rst |
uvw |
B |
11 |
1990 |
bbb |
001 |
bb1 |
ggg |
def |
ghi |
A |
12 |
1190 |
bbb |
002 |
bb1 |
ggg |
def |
ghi |
A |
13 |
1990 |
bbb |
001 |
bb2 |
hhh |
jkl |
mno |
001 |
14 |
1990 |
bbb |
001 |
bb2 |
iii |
jkl |
mno |
001 |
15 |
1990 |
bbb |
002 |
bb2 |
kkk |
fgh |
ijk |
002 |
16 |
1990 |
bbb |
002 |
bb2 |
kkk |
fgh |
ijk |
002 |
17 |
1990 |
aaa |
003 |
bb1 |
jjj |
vwx |
cde |
B |
18 |
1990 |
aaa |
004 |
bb1 |
jjj |
vwx |
cde |
B |
19 |
1991 |
ccc |
001 |
bb1 |
lll |
bcd |
mno |
001 |
20 |
1991 |
ccc |
002 |
bb1 |
mmm |
qew |
pqr |
002 |
21 |
1991 |
ccc |
005 |
bb1 |
sdj |
djg |
jfk |
C |
22 |
1991 |
ccc |
006 |
bb1 |
sdj |
djg |
jfk |
C |
I can do some very basic programming in SAS but this is completely beyond the scope of my experience/expertise. Would really appreciate all help. Thanks in advance.
Try next code:
data want;
set have;
by year id component date;
retain indicator;
if first.date then indicator = 0;
if first.component and last.component /* only 1 row */
then indicator = AC_model;
else indicator +1;
run;
When I try to copy your data table to an editor,each cell is 3 lines from the previous cell. Could you put your data in the form of a data step? That would permit testable program suggestions.
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 25. 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.