I want to store values of ID in array when status is PROBATION. After that, I want to look up in the table the field ID that are in array to create a new record of them With the status changed (I want to maintain the original and the new record)
data have;
input agreement $10. id status$15. ;
datalines;
0002255 1 PROBATION
0002255 2 NORMAL
0007777 3 NORMAL
0007777 4 NORMAL
0007777 5 NORMAL
0008888 6 PROBATION
0008888 7 NORMAL
0008888 8 NORMAL
run;
data want (drop=i);
set have;
/* by agreement;*/
output;
j=0;
i=0;
retain j;
retain i;
array ids [1000] _temporary_;
array var {1} id ;
if status='PROBATION' then
do;
i + 1;
ids{i}=id;
%put ids{i};
end;
if id=ids{j} then
do;
j+1;
status='OTHER';
output;
end;
run;
Ok. See if this gives you what you want.
Also, ID should equal 6 in obs #8, right?
data have;
input agreement $ id status $10. day;
datalines;
0002255 0 NORMAL 3
0002255 1 PROBATION 4
0002255 2 NORMAL 14
0007777 3 NORMAL 23
0007777 4 NORMAL 8
0007777 5 NORMAL 9
0008888 6 PROBATION 11
0008888 7 NORMAL 12
0008888 8 NORMAL 5
;
data want(drop=d dd);
set have;
if status ne "PROBATION" then d = day;
else do;
status = "OTHER";
dd = day;
day = d;
output;
status = "PROBATION";
day = dd;
end;
output;
retain d;
run;
Result:
agreement id status day 0002255 0 NORMAL 3 0002255 1 OTHER 3 0002255 1 PROBATION 4 0002255 2 NORMAL 14 0007777 3 NORMAL 23 0007777 4 NORMAL 8 0007777 5 NORMAL 9 0008888 6 OTHER 9 0008888 6 PROBATION 11 0008888 7 NORMAL 12 0008888 8 NORMAL 5
What does your desired output look like from this example data?
Have | |||
agreement | id | status | day |
0002255 | 0 | NORMAL | 3 |
0002255 | 1 | PROBATION | 4 |
0002255 | 2 | NORMAL | 14 |
0007777 | 3 | NORMAL | 23 |
0007777 | 4 | NORMAL | 8 |
0007777 | 5 | NORMAL | 9 |
0008888 | 6 | PROBATION | 11 |
0008888 | 7 | NORMAL | 12 |
0008888 | 8 | NORMAL | 5 |
Sorry for the bad explanation, the idea is to copy information of previous row when the next is PROBATION
Want | |||
agreement | id | status | day |
0002255 | 0 | NORMAL | 3 |
0002255 | 1 | OTHER | 3 |
0002255 | 1 | PROBATION | 4 |
0002255 | 2 | NORMAL | 14 |
0007777 | 3 | NORMAL | 23 |
0007777 | 4 | NORMAL | 8 |
0007777 | 5 | NORMAL | 9 |
0007777 | 5 | OTHER | 9 |
0008888 | 6 | PROBATION | 11 |
0008888 | 7 | NORMAL | 12 |
0008888 | 8 | NORMAL | 5 |
0008888 | 8 | NORMAL | 3 |
Ok. See if this gives you what you want.
Also, ID should equal 6 in obs #8, right?
data have;
input agreement $ id status $10. day;
datalines;
0002255 0 NORMAL 3
0002255 1 PROBATION 4
0002255 2 NORMAL 14
0007777 3 NORMAL 23
0007777 4 NORMAL 8
0007777 5 NORMAL 9
0008888 6 PROBATION 11
0008888 7 NORMAL 12
0008888 8 NORMAL 5
;
data want(drop=d dd);
set have;
if status ne "PROBATION" then d = day;
else do;
status = "OTHER";
dd = day;
day = d;
output;
status = "PROBATION";
day = dd;
end;
output;
retain d;
run;
Result:
agreement id status day 0002255 0 NORMAL 3 0002255 1 OTHER 3 0002255 1 PROBATION 4 0002255 2 NORMAL 14 0007777 3 NORMAL 23 0007777 4 NORMAL 8 0007777 5 NORMAL 9 0008888 6 OTHER 9 0008888 6 PROBATION 11 0008888 7 NORMAL 12 0008888 8 NORMAL 5
Thank so much. It is almost. I´ve got a mistake in the table. In the eight row should be 5.
The extra row was a mistake of copy-paste.
agreement | id | status | day |
0002255 | 0 | NORMAL | 3 |
0002255 | 0 | OTHER | 3 |
0002255 | 1 | PROBATION | 4 |
0002255 | 2 | NORMAL | 14 |
0007777 | 3 | NORMAL | 23 |
0007777 | 4 | NORMAL | 8 |
0007777 | 5 | NORMAL | 9 |
0007777 | 5 | OTHER | 9 |
0008888 | 6 | PROBATION | 11 |
0008888 | 7 | NORMAL | 12 |
0008888 | 8 | NORMAL | 5 |
Then why is ID equal to 1 in the second obs?
Also, why does id 8 get an additional obs?
Hi @t34 If I understand you correctly, you need LOOK AHEAD-
data have;
input (agreement id status) (:$10.) day;
cards;
0002255 0 NORMAL 3
0002255 1 PROBATION 4
0002255 2 NORMAL 14
0007777 3 NORMAL 23
0007777 4 NORMAL 8
0007777 5 NORMAL 9
0008888 6 PROBATION 11
0008888 7 NORMAL 12
0008888 8 NORMAL 5
;
data want;
merge have have(keep=status firstobs=2 rename=(status=_status));
output;
if _status='PROBATION' then do;
status='OTHER';
output;
end;
drop _status;
run;
If your result is as simple as I think, you can do this. No need for array logic
data want;
set have;
output;
if status = "PROBATION" then do;
status='OTHER';
output;
end;
run;
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.