topic Re: integrate rows in SAS Programming

integrate rows

JHE — Tue, 08 Jan 2019 18:07:04 GMT

I have the records as :

LOB	MEMBER_ID	Asthma	COPD
0100	200000104	N	Y
0100	200000104	Y	N

how could I can have result as this :

LOB	MEMBER_ID	Asthma	COPD
0100	200000104	Y	Y

I do not know how to

Thank you

Re: integrate rows

Reeza — Tue, 08 Jan 2019 18:09:11 GMT

Assuming the only values are Y and N, you can use a few methods, but a nifty one is the SAS SQL MAX() function. It will take character variables and since Y > N alphabetically it's the max.

proc sql;
create table want as 
select ID, member_ID, max(asthma) as asthma, max(copd) as copd
from have
group by id, member_id;
quit;

Re: integrate rows

JHE — Tue, 08 Jan 2019 18:15:38 GMT

Thank you,! works!

Re: integrate rows

ballardw — Tue, 08 Jan 2019 18:28:52 GMT

My first suggestion would be to not code values as character for things that take Yes/No True/False types of values. If you code values of Yes or True as 1 and No of False as 0 then you can do a number of things.

In this case a maximum value would return 1 if any of the values for group are 1 and 0 otherwise. Also you can get counts of Yes by summing the value, and percentage Yes by taking a mean.

Here is an example both ways, adding a numeric to use Proc Summary to get the values (and a format to show Y/N) and the kind of code that is needed to do character comparisons. I also included somewhat more complex example as a solution that works for exactly 2 records may not work for 3 or more. Also, please note the method of providing a data step to show example data. With that code people can test code against your data.

data example;
   input lob :$4. Member_id :$9. Asthma :$1. COPD :$1.;
   Nasthma= (asthma='Y');
   Ncopd  = (copd='Y');
datalines;
0100  200000104 N Y
0100  200000104 Y N
0100  200000105 N Y
0100  200000105 Y N
0100  200000105 N N
0100  200000105 N N
0100  200000106 Y N
0100  200000106 N N
;
run;

proc sort data=example;
  by lob member_id;
run;
/* character manipulation*/
data want;
   set example;
   by lob member_id;
   length tasthma tcopd $ 1;
   Retain tasthma tcopd;
   if first.member_id then do;
      tasthma = asthma;
      tcopd = copd;
   end;
   else do;
      if tasthma='Y' then asthma=tasthma;
      else if asthma='Y' then tasthma='Y';
      if tcopd='Y' then copd=tcopd;
      else if copd='Y' then tcopd='Y';
  end;
  if last.member_id;
  drop tasthma tcopd;
run;
/* numeric and format if desired*/
proc format library=work;
value yn
0='N'
1='Y'
;
run;
proc summary data=example nway;
   class lob member_id;
   var nasthma ncopd;
   output out=want2 (drop=_:) max=;
   format nasthma ncopd yn.;
run;

The above implies that what you are actually asking is "if ANY record has Y for Asthma then the final result should indicate Y for Asthma, similar of COPD and grouped by the combination of lob and member_id.

The sort would be required for the character comparison to get all of the like records together. The numeric approach with the CLASS variables in Proc summary does not.

Re: integrate rows

novinosrin — Tue, 08 Jan 2019 19:40:51 GMT

data example;
   input lob :$4. Member_id :$9. Asthma :$1. COPD :$1.;
 datalines;
0100  200000104 N Y
0100  200000104 Y N
0100  200000105 N Y
0100  200000105 Y N
0100  200000105 N N
0100  200000105 N N
0100  200000106 Y N
0100  200000106 N N
;
run;

proc sql;
create table want as 
select  LOB,MEMBER_ID,ifc(sum( Asthma='Y')>=1,'Y','N') as Asthma,ifc(sum( COPD='Y')>=1,'Y','N') as COPD
from example
group by lob,member_id;
quit;

This will handle all chars,nums etc