topic Re: sas data table transformation in SAS Data Management

sas data table transformation

Arora_S — Mon, 01 Feb 2016 16:39:34 GMT

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

Re: sas data table transformation

mohamed_zaki — Sun, 31 Jan 2016 01:26:36 GMT

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;

Re: sas data table transformation

Arora_S — Sun, 31 Jan 2016 23:35:37 GMT

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.

Re: sas data table transformation

mohamed_zaki — Sun, 31 Jan 2016 23:55:27 GMT

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

Re: sas data table transformation

Arora_S — Mon, 01 Feb 2016 18:35:14 GMT

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..

Re: sas data table transformation

Arora_S — Mon, 01 Feb 2016 18:34:48 GMT

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..