BookmarkSubscribeRSS Feed
Arora_S
Obsidian | Level 7

 

 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

5 REPLIES 5
mohamed_zaki
Barite | Level 11

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
Obsidian | Level 7

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

mohamed_zaki
Barite | Level 11

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

Arora_S
Obsidian | Level 7

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 975 views
  • 2 likes
  • 2 in conversation