sas data table transformation

[Arora_S]

 I want to transofrm data that looks like as below:

and has followingfields :--> patid, date on which a medical team was added/removed, type of team , and action

patid	date	Team	action
1111	jan-25-2016	medical	added
1111	jan-27-2016	surgical	added
1111	29-Jan	medical	removed
1111	31-Jan	obgyn	added

The output that i need shoud be in this way:so for each date i have the team in one row and for missing dates i can populate

them as below...what is the most efficent way to do this in sas base sas ??ANy help will behighly  appreciated...

thanks guys

patid	date	team1	team2	team3
1111	jan-25-2016	medical
1111	26-Jan	medical
1111	27-Jan	medical	surgical
1111	28-Jan	medical	surgical
1111	29-Jan	surgical
1111	30-Jan	surgical	obgyn

1111      31-jan      surgical    obgyn

[mohamed_zaki]

You posted your same question THREE times in THREE Community !!!!! Why?

One possible Solution:

data want;
set have;
by patid date;
array Team_[5] $;
retain Team_ arrindex;
if first.patid then do;arrindex=0; call missing (of Team_[*]);end;
if trim(action) eq "added" then do;
	arrindex = arrindex+1;
	Team_[arrindex]=team;
end;
else if trim(action) eq "removed" and arrindex ~= 0 then do;
	Do i=1 to dim(Team_) by 1;
		if  Team_[i]=team then do;
			do j=i to arrindex-1;
				Team_[j]=Team_[j+1];
			end;
			Team_[j]="";
			arrindex=arrindex-1;
			leave;
		end;
	end;
end;
else return;
drop i j arrindex;
run;

[mohamed_zaki]

You posted your same question THREE times in THREE Community !!!!! Why?

One possible Solution:

data want;
set have;
by patid date;
array Team_[5] $;
retain Team_ arrindex;
if first.patid then do;arrindex=0; call missing (of Team_[*]);end;
if trim(action) eq "added" then do;
	arrindex = arrindex+1;
	Team_[arrindex]=team;
end;
else if trim(action) eq "removed" and arrindex ~= 0 then do;
	Do i=1 to dim(Team_) by 1;
		if  Team_[i]=team then do;
			do j=i to arrindex-1;
				Team_[j]=Team_[j+1];
			end;
			Team_[j]="";
			arrindex=arrindex-1;
			leave;
		end;
	end;
end;
else return;
drop i j arrindex;
run;

[Arora_S]

I am Sorry it got posted 3 times by mistake as I wasn't sure which category is right to post in. Will avoid this duplicate submission in future. Thanks for the innovative solution and bringing this up.

[mohamed_zaki]

Then kindly if that match your requirements close the thread by marking the reply as "Accepted Solution".

[Arora_S]

The dates are not getting populated well in output

for example:

for missing dates , i want to populate them based on info from the previous non missing row as in output..

[Arora_S]

Its a partial solution as dates are not getting populated where sequence missing  but I find it very helpful to address problem using your approach..