topic Re: Proc Transpose using changing dates in SAS Procedures

Proc Transpose using changing dates

BETO — Wed, 09 Oct 2013 03:25:03 GMT

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 ;

Re: Proc Transpose using changing dates

Mit — Wed, 09 Oct 2013 04:18:07 GMT

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

Re: Proc Transpose using changing dates

BETO — Wed, 09 Oct 2013 19:15:21 GMT

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

Re: Proc Transpose using changing dates

Astounding — Wed, 09 Oct 2013 19:36:30 GMT

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 date:)=0))

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

Good luck.

Re: Proc Transpose using changing dates

BETO — Wed, 09 Oct 2013 21:02:30 GMT

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 .

Re: Proc Transpose using changing dates

BETO — Wed, 09 Oct 2013 21:05:40 GMT

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

Re: Proc Transpose using changing dates

Astounding — Wed, 09 Oct 2013 22:35:58 GMT

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.

Re: Proc Transpose using changing dates

art297 — Wed, 09 Oct 2013 23:17:35 GMT

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

Re: Proc Transpose using changing dates

Patrick — Wed, 09 Oct 2013 23:28:24 GMT

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

Re: Proc Transpose using changing dates

Mit — Thu, 10 Oct 2013 00:05:50 GMT

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

Re: Proc Transpose using changing dates

art297 — Thu, 10 Oct 2013 00:07:15 GMT

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

Re: Proc Transpose using changing dates

BETO — Thu, 10 Oct 2013 00:51:19 GMT

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

Starting Table

Atmcodenumber

DispCashAmt

TranDate

1320

10/2/2013

1320

10/3/2013

3540

10/3/2013

12180

10/4/2013

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)

Re: Proc Transpose using changing dates

BETO — Thu, 10 Oct 2013 01:07:42 GMT

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

Re: Proc Transpose using changing dates

art297 — Thu, 10 Oct 2013 01:25:13 GMT

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

Re: Proc Transpose using changing dates

BETO — Thu, 10 Oct 2013 04:13:06 GMT

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

;

Re: Proc Transpose using changing dates

art297 — Thu, 10 Oct 2013 04:49:18 GMT

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

Re: Proc Transpose using changing dates

art297 — Thu, 10 Oct 2013 15:01:03 GMT

: 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

Re: Proc Transpose using changing dates

BETO — Thu, 10 Oct 2013 18:13:45 GMT

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

Re: Proc Transpose using changing dates

art297 — Thu, 10 Oct 2013 18:36:04 GMT

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.

Re: Proc Transpose using changing dates

BETO — Fri, 11 Oct 2013 03:30:41 GMT

THanks Arthur it worked