topic Hi Arthur Error in flip transpose in SAS Procedures

Hi Arthur Error in flip transpose

BETO — Fri, 11 Oct 2013 23:03:50 GMT

HI Arthur

it only took 1 day and code stop working story of my life lol

I here is there error log

ERROR: Array subscript out of range at line 79 column 205.

Atmcodenumber=CA2644 DispCashAmt=1800.00 Date=9 FIRST.Atmcodenumber=0 LAST.Atmcodenumber=1

date1=839.00 date2=0.00 date3=0.00 date4=1045.00 date5=1200.00 date6=245.00 date7=0.00 date8=0.00

___nnum=9 ___i=1 _ERROR_=1 _N_=12804

ng this step because of errors.
NOTE: There were 12805 observations

NOTE: The SAS System stopped process
iread from the data set WORK._TEMP.

be incomplete. When this step was stopped there were 1854

WARNING: The data set WORK.WANT1 ma
y

here is that line #79script not sure if the error is in the macro or tables

%macro transpose(libname_in=,

libname_out=,

data=,

out=,

by=,

prefix=,

var=,

autovars=,

id=,

var_first=,

format=,

delimiter=,

copy=,

drop=,

sort=,

sort_options=,

use_varname=,

preloadfmt=,

guessingrows=,

newid=row);

/*Check whether the data and out parameters contain one or two-level filenames*/

%let lp=%sysfunc(findc(%superq(data),%str(%()));

%if &lp. %then %do;

%let rp=%sysfunc(findc(%superq(data),%str(%)),b));

%let dsoptions=%qsysfunc(substrn(%nrstr(%superq(data)),&lp+1,&rp-&lp-1));

%let data=%sysfunc(substrn(%nrstr(%superq(data)),1,%eval(&lp-1)));

%end;

%else %let dsoptions=;

%if %sysfunc(countw(&data.)) eq 2 %then %do;

%let libname_in=%scan(&data.,1);

%let data=%scan(&data.,2);

%end;

%else %if %length(&libname_in.) eq 0 %then %do;

%let libname_in=work;

%end;

%if %sysfunc(countw(&out.)) eq 2 %then %do;

%let libname_out=%scan(&out.,1);

%let out=%scan(&out.,2);

%end;

%else %if %length(&libname_out.) eq 0 %then %do;

%let libname_out=work;

%end;

THanks again

Re: Hi Arthur Error in flip transpose

BETO — Fri, 11 Oct 2013 23:20:10 GMT

HEre are rest of tables per your advise I remove sort

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

Unmark as Correct

Re: Hi Arthur Error in flip transpose

art297 — Sat, 12 Oct 2013 00:40:25 GMT

I'm confused!

I suggested running the following (assuming the following proc sql statement you specified creates the desired input data file):

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

);

quit;;

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

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

sort=yes)

BTW: I definitely don't need the points, but you marked your own response as being the correct one in your previous thread. That will definitely confuse those who discover the thread upon searching for a correct answer to a similar problem.

Re: Hi Arthur Error in flip transpose

BETO — Sat, 12 Oct 2013 18:59:15 GMT

HI Arthur, thank you for the heads up on the points tally I made the corrections.... The tables haven't change but I'm not getting this error message which is in the macro. Would you know what it means? An how to fix it ? Thanks again

Re: Hi Arthur Error in flip transpose

BETO — Sat, 12 Oct 2013 19:00:35 GMT

ERROR: Array subscript out of range at line 79 column 205. It would help if I add it to the message

Re: Hi Arthur Error in flip transpose

art297 — Sat, 12 Oct 2013 19:28:48 GMT

My guess would be that there are some records, above and beyond 1000, that have more rows. What happens when you remove the guessingrows' parameter. i.e.:

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

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

sort=yes)