Help using Base SAS procedures

Proc Transpose using changing dates

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

Proc Transpose using changing dates

HI I created a proc transpose that gives me a part of what i need .enclose is the proc transpose 

      090113.    090213.   090313

a1.        0      $67.          $100

A2.       0.                 0               0

A3.         $45.          $ 56.     0

i would like the proc transpose to select the I'd A2 since it had 0 all 3 days. if there was a better way I was hoping to replace the actual date to a static label date1 date2 date3.

proc transpose data=Active out=accounts (drop=_name_) prefix=_;

by Atmcodenumber;

var DispCashAmt;

id date1 ;



Accepted Solutions
Solution
‎10-10-2013 12:49 AM
PROC Star
Posts: 7,366

Re: Proc Transpose using changing dates

: 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.

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Transpose using changing dates


It's not clear. Could you please state clearly what is the input data and what is the expected output?

also give us the input data

thanks

Mit

Regular Contributor
Posts: 240

Re: Proc Transpose using changing dates

HI MIT,

tThe data sample looks like this

atmcodenumber.  Dips cash amt. date1

a1.                             $45.              09/01/13

a1.                              $23.              09/02/13

a1.                               $10.              09/03/13

a2.                               $53.              09/01/13

a2.                                  0.                 09/02/13

a2.                                  $33.              09/03/13

a3.                                      0.              09/01/13

a3.                                       0.             09/02/13

a3.                                       0.              09/03/13

in my output I would want to see A3 because it was 0 for 3 days unlike the other 2 that had amt in  one of the days.... Thanks

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Transpose using changing dates


Hi Beto

See if wanted this....

data have;
set TEST_DATA;
run;

/*Change Date1 to character*/

data have1;
set have;
day=day(date1);
year=year(date1);
month=month(date1);
run;
data have2;
set have1;
date2=compress(day||'/'||month||'/'||year);
run;
proc sort data= have2;
by Atmcodenumber;
run;
proc transpose data=have2 out=accounts (drop=_name_) ;
by Atmcodenumber;
var 'Dips cash amt'n;
id date2 ;
run;

Output:


atmcodenumber


1/09/2013


2/09/2013


3/09/2013


a1


45


23


10


a2


53


0


33


a3


0


0


0

Super User
Posts: 5,099

Re: Proc Transpose using changing dates

Replacing the actual date to get variable names is easy.  Change the prefix in the PROC statement:

prefix=date

Then remove the ID statement.

If you are sure to have exactly three dates for each ATMCODENUMBER, you could add a WHERE clause to the output data set:

out=accounts (drop=_NAME_ where=(date1=date2=date3=0))

If the number of dates might vary, you could try this but I'm not positive it works for an output date set:

out=accounts (drop=_NAME_ where=(sum(of dateSmiley Happy=0))

If it works, it would not necessarily be accurate if your data could contain negative numbers.

Good luck.

Regular Contributor
Posts: 240

Re: Proc Transpose using changing dates

Astounding,

i tried your suggestion  the first part  prefix=date an removing  ID statement. And what I get is

what I would like to see is

ATmcodenumber     

                                     Date1.      date2.         Date3

a1.                                 120.           43.              34

I Don"t need to know the date because it will always be 3 dates but the reason I can't have date appear is because every time I run the code the date will change from one week to the next .

Regular Contributor
Posts: 240

Re: Proc Transpose using changing dates

SOrry I just notice that my output didn't carry over

what I get is

atmcodenumber.    Label of former variable.     Date1

a1.                              Dispcashamt.                    45

a1.                               Dispcashamt.                   34

a1.                               Dispcashamt.                   23

Super User
Posts: 5,099

Re: Proc Transpose using changing dates

That doesn't match the program and data you posted, unless ... I hesitate to mention this because it is quite obscure and is probably not the case ... the values for ATMCODENUMBER all look like "a1." but actually contain some different unprintable characters somewhere after the third character.

More likely, you should post your log and confirm the structure of the input and output data.  There are many posters here who are familiar enough with PROC TRANSPOSE.  It won't take long to debug.

PROC Star
Posts: 7,366

Re: Proc Transpose using changing dates

: If I correctly understand what you are trying to do, a paper that , , Joe Whitehurst and I collaborated on (and presented at the most recent SAS Global Forum and MWSUG meetings) might provide a macro that does exactly what you want.

The paper and code can be found at:

A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

Given the following data:

data have;

  informat date1 mmddyy8.;

  format date1 mmddyy8.;

  informat Dips_cash_amt dollar12.;

  input atmcodenumber $  Dips_cash_amt date1;

  cards;

a1.   $45.   09/01/13

a1.   $23.   09/02/13

a1.   $10.   09/03/13

a2.   $53.   09/01/13

a2.     0.   09/02/13

a2.   $33.   09/03/13

a3.     0.   09/01/13

a3.     0.   09/02/13

a3.     0.   09/03/13

;

and running the following (after having run the code that is downloadable from the above site):

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

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

           sort=yes)

You will end up with a file (work.want) that looks like:

                 atmcodenumber    date1         date2          date3

                      a1.                   10              23              45

                      a2.                   33               0              53

                      a3.                    0               0               0

Of course, you could do it without the macro, but that would require modifying the dataset to include an extra variable.

Respected Advisor
Posts: 3,908

Re: Proc Transpose using changing dates

Thanks Art

I remembered that you were talking about going to present such a paper but missed so far the link to it.

Cheers

Patrick

PROC Star
Posts: 7,366

Re: Proc Transpose using changing dates

: The up-to-date code is posted at that link, but the updated paper will probably take another day or two.

After the MWSUG meeting, I decided to add two capabilities that weren't included in the paper, one of which turned out to be the kind of solution that the present OP seems to want (i.e., the automatic creation of a new id variable if one doesn't exist in the data), and another that allows one to include a where statement when one includes a data parameter (e.g.,

data=have (where=idnum le 10)   )

I'm going to try to update the paper by the end of the day, tomorrow, but I can't control whether more pressing matters might arise.

Regular Contributor
Posts: 240

Re: Proc Transpose using changing dates

Hi Arthur your code seems close to what I need enclose are sample tables

Starting Table

Atmcodenumber

DispCashAmt

TranDate

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

Transpose Script

proc sort data=Active;

by Atmcodenumber;

run;

proc transpose data=Active out=accounts (drop=_name_) prefix=_;

by Atmcodenumber;

var DispCashAmt;

id TranDate;

run;

OutPut Table

Atmcodenumber

_LABEL_

10/2/2013

10/3/2013

10/4/2013

A1

DispCashAmt

1320

A2

DispCashAmt

0

A3

DispCashAmt

80

A1

DispCashAmt

1320

A2

DispCashAmt

0

A3

DispCashAmt

3540

A1

DispCashAmt

12180

A2

DispCashAmt

0

A3

DispCashAmt

2900

What I need to extract from the Output Table is  A2 because they had 0 DispCashAmt in the 3 days. OR another way to get what I need is The report is run weekly so the dates will change week to week How can I make it so the Dates field  is generic  and not hard coded? (E.G. Date1 Date2, Date3)

Regular Contributor
Posts: 240

Re: Proc Transpose using changing dates

Here are the tables

Starting Table

Atmcodenumber

DispCashAmt

TranDate

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

OutPut Table

Atmcodenumber

_LABEL_

10/2/2013

10/3/2013

A1

DispCashAmt

1320

A2

DispCashAmt

0

A3

DispCashAmt

80

A1

DispCashAmt

1320

A2

DispCashAmt

0

A3

DispCashAmt

3540

A1

DispCashAmt

12180

A2

DispCashAmt

0

THe transpose script is above---- what I need to extract from the output table is A2 they had 0  dispcashamt in the 3 days or another way to get what I need is the report is run weekly so the dates will change week to week How came I make it so the dates defied is generic and not hard coded?(E.G date1,date2, date,3).  Thanks you sorry for the format had hard time pasting iPadiPad

PROC Star
Posts: 7,366

Re: Proc Transpose using changing dates

: BTW, in case you missed any other papers I may have mentioned, sasCommunity.org now has a page that automatically updates one's presentation resume.  Mine can be found at: Presentations:Art297 Papers and Presentations - sasCommunity

Regular Contributor
Posts: 240

Re: Proc Transpose using changing dates

HI Arthur I'm still having issue when I run the code when I make refer to the actual table. When I run the code as you provided it works here are the tables minus the macro

proc sql

CREATE TABLE have AS select * from connection to ODBC

(SELECT

tbATM.Atmcodenumber,

tBT.DispCashAmt,

TBT.TranDate

FROM tbAtm,TBTSETTLEMENTHIST

WHERE TBT.AtmCodeNumber=tbATM.AtmCodeNumbeR

);

run;

proc sort data=Have;

by TranDate;

run;

proc sort data =Have

Out =Have

nodupkey;

by Atmcodenumber;

run;

WHen the below data step runs I get zero entries

data Have1;

SET Have;

  informat TranDate mmddyy8.;

  format TranDate mmddyy8.;

  informat DispCashAmt dollar12.;

  input Atmcodenumber $ DispCashAmt TranDate  ;

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

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

           sort=yes)

WHen the transpose fin running the output is

ATmcodenumber.     Date1

A001                34

A002               67

THanks again

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 528 views
  • 4 likes
  • 5 in conversation