Longitudinal to wide

[SASPhile]

For  data shown below, I need to convert the data based on FSDT. A contract can have multiple HCCs.But on the same FSDT if it has more than one HCC that needs to be displayed as shown in second data layout.Any help is appreciated.

contract        FSDT              DxDEN          HCC
1001        01/01/2010            43002          HCC-108
1001        01/01/2010            43004          HCC-109
1001        03/01/2010            43004          HCC-109
1002        03/01/2010            43002          HCC-108
1003        04/01/2010            43004          HCC-109
1003        04/01/2010            43006          HCC-90
1003        04/01/2010            43002          HCC-108
1003        05/01/2010            43002          HCC-108

Contract    FSDT                  Dxden           HCC1           HCC2          HCC3
1001        01/01/2010            43002          HCC-108       HCC-109
1001        03/01/2010            43004          HCC-109
1002        03/01/2010            43002          HCC-108
1003        04/01/2010            43006          HCC-90        HCC-108        HCC-109
1003        05/01/2010            43002          HCC-108

[Tom · Accepted solution]

Proc Transpose can do that. Getting to pick only the first value of DXDEN is a little tricky though.

proc transpose prefix=hcc out=want(where=(_name_ ne ' ')) ;

  by contract fsdt ;

  copy dxden;

  var hcc;

run;

[Linlin]

data have;

informat fsdt mmddyy10.;

format fsdt mmddyy10.;

input contract  :FSDT : DxDEN : HCC $8.;

cards;

1001        01/01/2010            43002  HCC-108

1001        01/01/2010            43004  HCC-109

1001        03/01/2010            43004  HCC-109

1002        03/01/2010            43002  HCC-108

1003        04/01/2010            43004  HCC-109

1003        04/01/2010            43006  HCC-90

1003        04/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

;

data temp;

  set have;

   by contract FSDT;

  count+first.fsdt;

proc sort;by count;

data want(drop=hcc count ct);

set temp;

by count;

array h{*}$ hcc1-hcc3;

retain  contract FSDT DxDEN hcc:;

if first.count then ct=0;

ct+1;

h{ct}=hcc;

if last.count then do;output; call missing(of h{*});

end;

run;

proc print;run;

Obs          fsdt             contract    DxDEN     hcc1          hcc2            hcc3

       1     01/01/2010      1001      43004    HCC-108    HCC-109

       2     03/01/2010      1001      43004    HCC-109

       3     03/01/2010      1002      43002    HCC-108

       4     04/01/2010      1003      43002    HCC-109    HCC-90     HCC-108

       5     05/01/2010      1003      43002    HCC-108

[rtritz]

Hello SASPhile, As Tom indicated proc transpose plus a data step should get the job done as well.

data contract;  

input contract FSDT mmddyy10. DxDEN HCC $;   format fsdt mmddyy10.;

datalines;

1001 01/01/2010 43002 HCC-108

1001 01/01/2010 43004 HCC-109

1001 03/01/2010 43004 HCC-109

1002 03/01/2010 43002 HCC-108

1003 04/01/2010 43004 HCC-109

1003 04/01/2010 43006 HCC-90

1003 04/01/2010 43002 HCC-108

1003 05/01/2010 43002 HCC-108

;

run;

proc transpose data = contract out=twide;  

by contract fsdt;  

copy dxden;  

var hcc;

run;

data wide (drop=_name_);  

set twide;  

if _name_ = '' then delete;  

rename col1 = HCC1 col2 = HCC2 col3 = HCC3;

run;

HTH,

Rich

[Haikuo]

Like Tom said, choose the right Dxden can be tricky.

Here is another version based on LinLin's code, it managed to meet op's need. One thing I don't like is that it is still involving some hard coding.

data have;

informat fsdt mmddyy10.;

format fsdt mmddyy10.;

input contract  :FSDT : DxDEN : HCC $8.;

cards;

1001        01/01/2010            43002  HCC-108

1001        01/01/2010            43004  HCC-109

1001        03/01/2010            43004  HCC-109

1002        03/01/2010            43002  HCC-108

1003        04/01/2010            43004  HCC-109

1003        04/01/2010            43006  HCC-90

1003        04/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

;

data have;

set have;

order=input(substr(hcc,anyalpha(hcc,-10)+2),3.);

run;

proc sort data=have;

by contract fsdt order;

run;

data want (drop=hcc dxden order rename=dx=DxDen);

  do _n_=1 by 1 until (last.fsdt);

      set have;

      by contract fsdt order;

      array h $ hcc1-hcc3;

      if first.fsdt then dx=dxden;

      h(_n_)=hcc;

  end;

  output;

run;

proc print;run;

[SASPhile]

Thanks Hai.Kuo

[SASPhile]

Hi Hai.Kuo,

For the data below HCC is missing for the last record.If I apply the same logic as yours,I'm getting an error "

Array subscript out of range".

data have;

informat fsdt mmddyy10.;

format fsdt mmddyy10.;

input contract  :FSDT : DxDEN : HCC $8.;

cards;

1001        01/01/2010            43002  HCC-108

1001        01/01/2010            43004  HCC-109

1001        03/01/2010            43004  HCC-109

1002        03/01/2010            43002  HCC-108

1003        04/01/2010            43004  HCC-109

1003        04/01/2010            43006  HCC-90

1003        04/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1004        05/01/2010            43008 

;

[Tom]

The only way to get an array out of bound would be if there were more than 3 observations for the same CONTRACT/FSDT combination.

You might want to change the upperbound and the ARRAY statement if 3 is not enough.

This is why PROC TRANSPOSE is more convenient.  You do not need to count in advance how many possible values there are.

[SASPhile]

I wish there is a wildcard kind of operator to define upper bound.

[Haikuo]

That is odd. I can't repeat your problem. You need to make sure that there is no empty rows in your 'have' table which may require you to expand  array range.

Run this one to decide how many elements you are going to need in your array:

proc sql;

select max(ct) from

(select (count(*)) as ct from have

group by contract,fsdt)

;

quit;

HTH,

Haikuo

[Linlin]

I don't have any problem to run your code with the new dataset:

data have;

informat fsdt mmddyy10.;

format fsdt mmddyy10.;

input contract  :FSDT : DxDEN : HCC $8.;

cards;

1001        01/01/2010            43002  HCC-108

1001        01/01/2010            43004  HCC-109

1001        03/01/2010            43004  HCC-109

1002        03/01/2010            43002  HCC-108

1003        04/01/2010            43004  HCC-109

1003        04/01/2010            43006  HCC-90

1003        04/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1004        05/01/2010            43008

;

data have;

set have;

order=input(substr(hcc,anyalpha(hcc,-10)+2),3.);

run;

proc sort data=have;

by contract fsdt order;

run;

data want (drop=hcc dxden order rename=dx=DxDen);

  do _n_=1 by 1 until (last.fsdt);

      set have;

      by contract fsdt order;

      array h $ hcc1-hcc3;

      if first.fsdt then dx=dxden;

      h(_n_)=hcc;

  end;

  output;

run;

proc print;run;

Obs         fsdt   contract    hcc1      hcc2      hcc3     DxDen

1    01/01/2010     1001     HCC-108   HCC-109             43002

2    03/01/2010     1001     HCC-109                       43004

3    03/01/2010     1002     HCC-108                       43002

4    04/01/2010     1003     HCC-90    HCC-108   HCC-109   43006

5    05/01/2010     1003     HCC-108                       43002

6    05/01/2010     1004                                   43008

[SASPhile]

This works fine. As Tom mentioned, if there are more than three records per FSDT then it will go out of range.For instance the following gives an error.

data have;

informat fsdt mmddyy10.;

format fsdt mmddyy10.;

input contract  :FSDT : DxDEN : HCC $8.;

cards;

1001        01/01/2010            43002  HCC-108

1001        01/01/2010            43004  HCC-109

1001        03/01/2010            43004  HCC-109

1002        03/01/2010            43002  HCC-108

1003        04/01/2010            43004  HCC-109

1003        04/01/2010            43006  HCC-90

1003        04/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1004        05/01/2010            43008

;

[Linlin]

The modified code takes care of the "out of range" problem.

data have;

informat fsdt mmddyy10.;

format fsdt mmddyy10.;

input contract  :FSDT : DxDEN : HCC $8.;

cards;

1001        01/01/2010            43002  HCC-108

1001        01/01/2010            43004  HCC-109

1001        03/01/2010            43004  HCC-109

1002        03/01/2010            43002  HCC-108

1003        04/01/2010            43004  HCC-109

1003        04/01/2010            43006  HCC-90

1003        04/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1003        05/01/2010            43002  HCC-108

1004        05/01/2010            43008

;

proc sql noprint;

  select max(groups) into: maxgroup

    from (select count(*) as groups from have group by contract,fsdt);

quit;

%let maxgroup=&maxgroup;

data have;

set have;

order=input(substr(hcc,anyalpha(hcc,-10)+2),3.);

run;

proc sort data=have;

by contract fsdt order;

run;

data want (drop=hcc dxden order rename=dx=DxDen);

  do _n_=1 by 1 until (last.fsdt);

      set have;

      by contract fsdt order;

      array h $ hcc1-hcc&maxgroup;

      if first.fsdt then dx=dxden;

      h(_n_)=hcc;

  end;

  output;

run;

proc print;run;

[art297]

Since this thread continues to live, I'll propose another alternative:

proc sql noprint;

   select max(obs) into: obs

     from (select count(*) as obs

       from have group by contract,fsdt)

  ;

quit;

proc summary data=have nway;

   class contract fsdt;

   output out=want(drop=_:)

      idgroup(out[&obs](DxDEN HCC)=)

      ;

run;

data want (drop=dxDEN_:);

  set want (rename=(DxDEN_1=DxDen));

run;

[data_null__]

You can use as many IDGROUP options as necessary.  The default is to pick the FIRST value, as is desired here.

proc summary data=have nway;

   class contract fsdt;

   output out=want(drop=_:)

      idgroup(out[&obs](HCC)=)

     idgroup(out(dxden)=)

      ;

   run;

I think Tom's use of COPY statement is really nice.