SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

sas data table transformation

Reply
Contributor
Posts: 35

sas data table transformation

[ Edited ]

 

 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

Super Contributor
Posts: 490

Re: sas data table transformation

[ Edited ]

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;
Contributor
Posts: 35

Re: sas data table transformation

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

Super Contributor
Posts: 490

Re: sas data table transformation

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

Contributor
Posts: 35

Re: sas data table transformation

[ Edited ]

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

Contributor
Posts: 35

Re: sas data table transformation

[ Edited ]

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

Ask a Question
Discussion stats
  • 5 replies
  • 349 views
  • 2 likes
  • 2 in conversation