BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASPhile
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

17 REPLIES 17
Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Onyx | Level 15

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
Quartz | Level 8

Thanks Hai.Kuo

SASPhile
Quartz | Level 8

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
Super User Tom
Super User

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
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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__
Jade | Level 19

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 1918 views
  • 10 likes
  • 8 in conversation