topic Re: ARRAY PROBLEMS WHEN THERE ARE NO RECORDS in SAS Procedures

ARRAY PROBLEMS WHEN THERE ARE NO RECORDS

JasonNC — Mon, 24 Sep 2012 18:39:53 GMT

Hi,

I am having a problem with arrays when there are no records in the dataset

PROC SQL NOPRINT;

SELECT COUNT(*) INTO :CNT

FROM DETAILS;

QUIT;

DATA TEST;

SET PERSONS;

IF &CNT > 0 THEN DO;

ARRAY DTL {&CNT} $10. _TEMPORARY_;

DO i=1 to &CNT;

DTL{i}=DETAIL;

OUTPUT;

END;

RUN;

I kept the condiiton to do it only in cases where the record count is > 0 but when i ran this program and there is no data it throws the error

ERROR: Invalid dimension specification for array DTL. The upper bound of an array dimension is smaller than its corresponding

lower bound.

Why it is generating this error even when i kept the condition &CNT > 0 .

Re: ARRAY PROBLEMS WHEN THERE ARE NO RECORDS

Tom — Mon, 24 Sep 2012 19:00:04 GMT

Array statements are not executable. They are just used to setup the data step. So it is the same as if the ARRAY statement is outside of the IF/THEN block. Same for other non-executable statements like KEEP/DROP/RENAME/ etc.

Your example program does not make any sense. You are assigning values to temporary variables that will just be thrown away.

What do you actually want to do? Perhaps there is a better way to do it?

Re: ARRAY PROBLEMS WHEN THERE ARE NO RECORDS

mkeintz — Mon, 24 Sep 2012 19:36:30 GMT

Tom has pointed out the erroneous assumptions your program embodies about the non-executable nature of the ARRAY statement, and even if the program ran to completion it wouldn't do anything.

But assuming the program did do some real work, (say you were looking for the 2nd largest value in a collection), you could still use &CNT as an upper bound if you used 0 as a lower bound, as in

ARRAY DTL {0:&CNT} $10. _TEMPORARY_;

The array statement is still not executable, but if &CNT is zero, this statement would have avoided the implicit definition of an array with lower bound of 1 (the default) and upper bound of 0. That's what provoked the "upper bound of an array is smaller than ..." message.

Re: ARRAY PROBLEMS WHEN THERE ARE NO RECORDS

JasonNC — Mon, 24 Sep 2012 19:43:28 GMT

Hi Tom,

I am doing a cartesian join there.

For example i have a dataset like this Persons

Persons

John

Tom

Jim

Drug Tests

i am creating a data set like this

John TB

John BB

John CF

JIm TB

Jim BB

JIm CF

Re: ARRAY PROBLEMS WHEN THERE ARE NO RECORDS

Reeza — Mon, 24 Sep 2012 19:52:52 GMT

Cross join in SQL instead?

proc sql;

create table want as

select p.*, t.*

from persons p

cross join

tests t;

quit;

Re: ARRAY PROBLEMS WHEN THERE ARE NO RECORDS

Tom — Mon, 24 Sep 2012 19:57:56 GMT

SQL is the easiest.

To do it with a data step you do not need any macro variables.

data want ;

set persons;

do i=1 to nobs;

set drugtests nobs=nobs point=i;

output;

end;

run;

If you want to keep the people when there are no drug tests you can add.

if nobs=0 then output;