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.
THanks Arthur it worked
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
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
;
: 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.