BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

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

5 REPLIES 5
BETO
Fluorite | Level 6

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

art297
Opal | Level 21

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.


BETO
Fluorite | Level 6

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

BETO
Fluorite | Level 6

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

art297
Opal | Level 21

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)


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 741 views
  • 0 likes
  • 2 in conversation