Proc Transpose using changing dates

[art297]

Leave out the two sort steps as the macro will sort the file.  The problem you are having is your 2nd sort, where you are sorting using the nodupkey option.  That is deleting all but one record for each atmcodenumber.

[BETO]

THanks Arthur it worked

[art297]

My suggestion was to avoid proc transpose altogether.  Given your example data set:

data have;

  informat TranDate mmddyy8.;

  format TranDate mmddyy8.;

  informat DispCashAmt dollar12.;

  input Atmcodenumber $ DispCashAmt TranDate  ;

  cards;

A1   1320  10/2/2013

A2      .  10/2/2013

A3     80  10/2/2013

A1   1320  10/3/2013

A2      .  10/3/2013

A3   3540  10/3/2013

A1  12180  10/4/2013

A2      .  10/4/2013

A3   2900  10/4/2013

;

and running the following (after downloading and running the macro I pointed to in my previous post):

%transpose(data=have, out=want, var=DispCashAmt, by=atmcodenumber,

           newid=date, prefix=date, use_varname=no, guessingrows=1000,

           sort=yes)

Produces:

Atmcodenumber    date1          date2           date3

     A1                  1320            1320           12180

     A2                     .               .               .

     A3                    80            3540            2900

[BETO]

HI Arthur,

couple of questions the table I'm using is name Active in your example 1st part I change have to Active? Since its making refer to a table I remove the data from cards down to ;  ? Do I insert a Run;?

2nd question I copy your example code it's making reference to Art lib which is fails.base on the cols I provided do I have to  tweak the code? Thanks again

data have;

  informat TranDate mmddyy8.;

  format TranDate mmddyy8.;

  informat DispCashAmt dollar12.;

  input Atmcodenumber $ DispCashAmt TranDate  ;

  cards;

A1   1320  10/2/2013

A2      .  10/2/2013

A3     80  10/2/2013

A1   1320  10/3/2013

A2      .  10/3/2013

A3   3540  10/3/2013

A1  12180  10/4/2013

A2      .  10/4/2013

A3   2900  10/4/2013

;

[art297 · Accepted solution]

: My error!  In my haste I uploaded a draft version of the revised program.  Please delete the copy you downloaded and download the new version from: A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

There should no longer be any reference in that code to a libname called Art

As for your data, in the first part of the statement, below (data=have), have should be replaced with whatever your datafile is called.  If I correctly read your question, you would replace it with Active if the file is in your work directory.  However, if it is in a library (e.g., mydata) that you've already assigned, then the first part of the macro call would be:

%transpose(data=mydata.active, etc., etc.).  Similarly, The out file doesn't have to be called 'want' and it, too, can be either a one or two level filename.

%transpose(data=have, out=want, var=DispCashAmt, by=atmcodenumber,

           newid=date, prefix=date, use_varname=no, guessingrows=1000,

           sort=yes)

As for the code I used to create your data, a run statement isn't needed, only the semi-colon on the line right after your last data line.  Some people prefer to include a run statement as well, but SAS will gladly create the same file with or without it.