DATA Step, Macro, Functions and more

SAS Data Manipulation - Interesting Question

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

SAS Data Manipulation - Interesting Question

[ Edited ]

 I want to transform data that looks like as below:

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

Input data --à

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 should 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 efficient way to do this in sas base /sas ??Any help will be highly appreciated...

Thanks guys

Output Data --à

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

 

 

1111      31- jan     surgical   obgyn


Accepted Solutions
Solution
‎02-01-2016 11:36 AM
Super User
Posts: 10,028

Re: SAS Data Manipulation - Interesting Question

Your output doesn't look like right .

 

1111

29-Jan

surgical

 

 

1111

30-Jan

surgical

obgyn

 

 

31-Jan is after 30-Jan . How can you put obgyn   into  30-Jan?

 

 

data have;
input patid date : date11. Team $ action $;
format date  date11. ;
cards;
1111 25-jan-2016 medical added
1111 27-jan-2016 surgical added
1111 29-Jan-2016 medical removed
1111 31-Jan-2016 obgyn added
;
run;
data temp;
 merge have have(keep=patid date rename=(patid=_patid  date=_date) firstobs=2);
if _n_ eq 1 then do;
 declare hash h();
 declare hiter hi('h');
 h.definekey('team');
 h.definedata('team');
 h.definedone();
end;
if patid ne _patid then h.clear();
if action='added' then rc=h.add();
if action='removed' then rc=h.remove();
if patid=_patid then do;
 do i=date to _date-1;
 date=i;
  do while(hi.next()=0);
   output;
  end;
 end;
end;
drop rc i _: action;
run;
proc sort data=temp;by patid date Team;run;
proc transpose data=temp out=want(drop=_name_) prefix=team ;
 by patid date;
 var team;
run; 

View solution in original post


All Replies
Respected Advisor
Posts: 4,925

Re: SAS Data Manipulation - Interesting Question

Do not post the same question on multiple communities.

 

Here is a single data step solution, assuming your input dataset is called have and your date is a SAS date:

 


data want;
set have; by patid;
prevDate = lag(date);
array teams{*} $12 teams1-teams5 ("");
if first.patid then do;
    call missing (of teams{*});
    prevDate = date;
    end;
else do;
    prevDate = intnx("DAY", prevDate, 1);
    do while(prevDate < date);
        output;
        prevDate = intnx("DAY", prevDate, 1);
        end;
    end;
select (action);
    when ("added") do;
        if team not in teams then do;
            do pos = 1 by 1 until(missing(teams{pos})); end;
            teams{pos} = team;
            end;
        end;
    when ("removed") do;
        pos = whichc(team, of teams{*});
        if pos > 0 then do;
            do i = pos+1 to dim(teams);
                teams{i-1} = teams{i};
                end;
            end;
        end;
    otherwise;
    end;
output;
drop pos i date team action;
format prevDate yymmdd10.;
rename prevDate=date;
run;
PG
Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

[ Edited ]

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 response and sharing your approach to this data management problem.

Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

Very Innovative approach especially for handling dates part ..

Solution
‎02-01-2016 11:36 AM
Super User
Posts: 10,028

Re: SAS Data Manipulation - Interesting Question

Your output doesn't look like right .

 

1111

29-Jan

surgical

 

 

1111

30-Jan

surgical

obgyn

 

 

31-Jan is after 30-Jan . How can you put obgyn   into  30-Jan?

 

 

data have;
input patid date : date11. Team $ action $;
format date  date11. ;
cards;
1111 25-jan-2016 medical added
1111 27-jan-2016 surgical added
1111 29-Jan-2016 medical removed
1111 31-Jan-2016 obgyn added
;
run;
data temp;
 merge have have(keep=patid date rename=(patid=_patid  date=_date) firstobs=2);
if _n_ eq 1 then do;
 declare hash h();
 declare hiter hi('h');
 h.definekey('team');
 h.definedata('team');
 h.definedone();
end;
if patid ne _patid then h.clear();
if action='added' then rc=h.add();
if action='removed' then rc=h.remove();
if patid=_patid then do;
 do i=date to _date-1;
 date=i;
  do while(hi.next()=0);
   output;
  end;
 end;
end;
drop rc i _: action;
run;
proc sort data=temp;by patid date Team;run;
proc transpose data=temp out=want(drop=_name_) prefix=team ;
 by patid date;
 var team;
run; 
Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

[ Edited ]

data row is not showing up for 31st jan .i.e last row is not showing up in the output dataset... It stops at 30th jan populating that row only.. Logic is correct !!! and code is giving right results till 2nd last row only !!.

 

Super User
Posts: 10,028

Re: SAS Data Manipulation - Interesting Question

Yeah. But your output didn't show the last one .

 

 

data have;
input patid date : date11. Team $ action $;
format date  date11. ;
cards;
1111 25-jan-2016 medical added
1111 27-jan-2016 surgical added
1111 29-Jan-2016 medical removed
1111 31-Jan-2016 obgyn added
;
run;
data temp;
 merge have have(keep=patid date rename=(patid=_patid  date=_date) firstobs=2);
if _n_ eq 1 then do;
 declare hash h();
 declare hiter hi('h');
 h.definekey('team');
 h.definedata('team');
 h.definedone();
end;
if action='added' then rc=h.add();
if action='removed' then rc=h.remove();
if patid=_patid then do;
 do i=date to _date-1;
 date=i;
  do while(hi.next()=0);
   output;
  end;
 end;
end;
else do;
  do while(hi.next()=0);
   output;
  end;
  h.clear();
end;
drop rc i _: action;
run;
proc sort data=temp;by patid date Team;run;
proc transpose data=temp out=want(drop=_name_) prefix=team ;
 by patid date;
 var team;
run; 
Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

Thanks so much, this solution is very innovative . Declare and hash are so useful in sas programs..

Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

[ Edited ]

Another interesting situation:

if data looks like this and first row has no data but want to fill it based on info from next row

Then how hash logic will change, Example as below

HAVE-->

patid   date      team     action

1111 24-jan 

1111 25-jan-2016 medical added

1111 27-jan-2016 surgical added

1111 29-Jan-2016 medical removed

1111 31-Jan-2016 surgical   removed

1111 04-feb    

 

 

WANT -->

 patid        date      team1     team2

1111        24-jan  medical            (Populating this row based on next non missing observation from 25th jan data)

1111        25-jan medical  

1111        26-jan  medical  

1111        27-jan medical   surgical

1111        28-jan medical   surgical

1111        29-jan    surgical

1111        30-jan    surgical

1111        31-jan   

1111        01-feb   

1111        02-feb                              

1111        03-feb                             

1111        04-feb                           

no data basically for last rows with no info on team and action, they are populated based on data  from 31st jan data row ?

 

Thanks

 

Super User
Posts: 10,028

Re: SAS Data Manipulation - Interesting Question

Ou, No. You have so complicated logic . what if there are multi obs has missing team at the first place , what you gonna do ?

patid   date      team     action

111   23-jan

1111 24-jan 

1111 25-jan-2016 medical added

 

 

 

 

data have;
input patid date : date11. Team $ action $;
format date  date11. ;
cards;
1111 24-jan-2016 . . 
1111 25-jan-2016 medical added
1111 27-jan-2016 surgical added
1111 29-Jan-2016 medical removed
1111 31-Jan-2016 surgical   removed
1111 04-feb-2016 . .   
;
run;
data temp;
 merge have have(keep=patid date team rename=(patid=_patid  date=_date team=_team) firstobs=2);
if _n_ eq 1 then do;
 declare hash h();
 declare hiter hi('h');
 h.definekey('team');
 h.definedata('team');
 h.definedone();
end;
if action='added' then rc=h.add();
if action='removed' then rc=h.remove();
lag_patid=lag(patid);
if patid=_patid then do;
 do i=date to _date-1;
 date=i;
  do while(hi.next()=0);
   output;
  end;
  if h.num_items=0 then do;
    if lag_patid ne patid then do;team=_team;output;end;
      else do;call missing(team);output;end;
  end;
 end;
end;
else do;
  do while(hi.next()=0);
   output;
  end;
  if h.num_items=0 then do;call missing(team);output;end;
  h.clear();
end;
drop rc i _: action;
run;
proc sort data=temp;by patid date Team;run;
proc transpose data=temp out=want(drop=_name_) prefix=team ;
 by patid date;
 var team;
run; 

 

Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

Not making this more complicated , just trying to learn all possible scenarios in which a data problem can be addressed.

Thanks so much for sharing your approach to this problem.

 

Contributor
Posts: 35

Re: SAS Data Manipulation - Interesting Question

[ Edited ]

Yes obgyn actually should be in 31st Jan row along with surgical. You're right. Thanks for noticing it and thanks for your useful innovative response.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 436 views
  • 4 likes
  • 3 in conversation