topic Longitudinal to wide in SAS Programming

Longitudinal to wide

SASPhile — Wed, 08 Feb 2012 22:16:35 GMT

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

Longitudinal to wide

Tom — Wed, 08 Feb 2012 22:47:40 GMT

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;

Longitudinal to wide

Linlin — Thu, 09 Feb 2012 00:53:23 GMT

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

Re: Longitudinal to wide

rtritz — Thu, 09 Feb 2012 03:57:49 GMT

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

Longitudinal to wide

Haikuo — Thu, 09 Feb 2012 04:12:37 GMT

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;

Longitudinal to wide

SASPhile — Thu, 09 Feb 2012 16:05:58 GMT

Thanks Hai.Kuo

Longitudinal to wide

SASPhile — Fri, 10 Feb 2012 19:26:52 GMT

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

;

Longitudinal to wide

Tom — Fri, 10 Feb 2012 20:02:35 GMT

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.

Longitudinal to wide

Haikuo — Fri, 10 Feb 2012 20:05:07 GMT

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

Re: Longitudinal to wide

Linlin — Fri, 10 Feb 2012 20:20:30 GMT

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

Longitudinal to wide

SASPhile — Fri, 10 Feb 2012 20:44:51 GMT

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

1004 05/01/2010 43008

;

Longitudinal to wide

SASPhile — Fri, 10 Feb 2012 20:46:49 GMT

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

Longitudinal to wide

Linlin — Fri, 10 Feb 2012 22:10:13 GMT

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

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;

Longitudinal to wide

art297 — Fri, 10 Feb 2012 22:33:23 GMT

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;

Longitudinal to wide

data_null__ — Fri, 10 Feb 2012 23:08:14 GMT

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.

Longitudinal to wide

art297 — Fri, 10 Feb 2012 23:28:08 GMT

I don't disagree. I just was surprised to see that the thread was still active and that no one had offered the proc summary solution that you and Mike Zdeb had written about.

Longitudinal to wide

Linlin — Sat, 11 Feb 2012 00:55:38 GMT

Hi Data _null_;

I just finished you and Mike's paper http://support.sas.com/resources/papers/proceedings10/102-2010.pdf. It is a very good paper. Thank you!

Linlin

Re: Longitudinal to wide

bnarang — Wed, 05 Dec 2012 08:24:33 GMT

HI Arthur

I tried but gave an error, if &obs is great than 100, then this does not work.