topic Re: Unknown upper bound array in listing variables in SAS Programming

Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:04:26 GMT

Hi all,

I have array code that works just fine.

array startx(3) start1 start2 start3;

But i have more than 3 possibles and i want to run it as an unknown upperbound. I know this:

array startx(*)

do i=1 to dim(startx);

and all that, but i can't figure out how to give a default base name so that sas can just number them as they come across them. Is there a way to do that? I've tried:

array startx(*) start:;

array startx(*) start*:

and nothing works.

I know that i can put a count into a macro variable and use that but because i'm working with millions of records and i'd rather not add the processing time. I was hoping i could do it right in the array line.

Thanks,
Megan

Re: Unknown upper bound array in listing variables

RW9 — Fri, 30 Jun 2017 16:16:35 GMT

Hi,

data have;
  start1=1; start2=3; start3=4;
run;
data want;
  set have;
  array startx start:;
  do i=1 to dim(startx);
    startx{i}=9;
  end;
run;

Although if you have lots of counts you would probably be better off having a normalised structure with data going down rather than across.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:20:44 GMT

Thanks. The data is currently going down and i want it to go across so i can compare end to start dates.

Doing it without the () and using the wildcard i'm getting warning saying it defined an array with zero elements.

Re: Unknown upper bound array in listing variables

RW9 — Fri, 30 Jun 2017 16:21:38 GMT

Sorry, I can't see your code/data or log??

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:26:40 GMT

I'm on an enclave computer that has no copy function or internet (security you know!). I can't copy anything over. The best i can do is a screenshot.

When the arrays are the normal "startx (3) start1 - start3" it runs just fine. I just need to know how to not have to write out start3. Leave it open for sas to fill.

Re: Unknown upper bound array in listing variables

mkeintz — Fri, 30 Jun 2017 16:30:38 GMT

By "the data is going down" do you mean that the "array" of values you want comes from the rows of data (i.e. one row per array element)? If so then the array size would equal the number of obs in the data set, which is easily determined.

But you also say you want to "compare end to start dates", which I guess means the date in the first obs vs the date in the last obs. In that case you don't even need the array. Just read the first obs and also used the "point=" option to read the last obs without reading any obs in between:

data _null_;

set have (obs=1) nobs=nrows;

set have (keep=date rename=(date=last_date)) point=nrows;

last_minus_first=last_date - date;

put date=date9. last_date=date9. last_minus_first=;

run;

Re: Unknown upper bound array in listing variables

RW9 — Fri, 30 Jun 2017 16:31:06 GMT

Its telling you there are no variables with start prefix. Your code seems confused, why is the set <dataset> within the do loop?

Your code should look like:

data...;
  set mods.mcare...;
  by usrds_id;
  array startx start:;
  ...;
  do i=1 to dim(startx);
    ...;
  end;
run;

Re: Unknown upper bound array in listing variables

RW9 — Fri, 30 Jun 2017 16:33:22 GMT

I think we have here another clear example of why following the guidance by the Post button is so key to getting clear answers.

1) Post test data in the form of a datastep

2) Post example required output

3) Explain logic between the two

4) Provide code/logs where present

We are just guessing here.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:33:47 GMT

I have 2 million rows of data, multiple rows per id, and i want one row per id with all of the start and end values on a single row.

When i'm going from "long to wide" i always put the arrays first listing out the new variables that i want to have in the new dataset i'm trying to create.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:35:59 GMT

I know the upper bound can be determined before hand, i said that in my initial post. What i also said is that because i'm working with millions of records, i'd rather not add to the processing time.

It looks like your option only allows me to compare two dates. I have multiple dates per id that i would like to see all on one line.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:37:31 GMT

Frankly, either there's a way to let sas determine the upper bound array name or there isn't. Me posting code isn't going to change whether or not someone knows if that's even possible. And i did post the array line. THe one line i needed help with.

Re: Unknown upper bound array in listing variables

RW9 — Fri, 30 Jun 2017 16:55:11 GMT

And following on from the minimum information you provided in the your first post:

---

array startx(3) start1 start2 start3;

But i have more than 3 possibles and i want to run it as an unknown upperbound. I know this:

array startx(*)

do i=1 to dim(startx);

---

I created this code, which runs fine:

data have;
  start1=1; start2=3; start3=4;
run;
data want;
  set have;
  array startx start:;
  do i=1 to dim(startx);
    startx{i}=9;
  end;
run;

Now you are telling us that your data does not have variables start1 start2 etc. Your data looks something like this:

START END MCARE

...

And you are putting these one by one up into variables, correct? This is different as there is no way for the datastep to know how many elements until the end.

So then you have several options:

- Find out how many items there are before your datastep;

proc sql;
  select max(count(start))
  into  :items
  from  have
  group by urds_id;
quit;

data want;
  set have;
  array startx{&items.)...;

This will likely be the quickest method.

You could do three transposes.

Or create a large array that will be bigger than you will ever get to.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 16:58:04 GMT

Correct, i only have start and end. I wanted to create start1 - start?.

And i want to know if SAS can determine the "?" for me.

Thank you

Re: Unknown upper bound array in listing variables

Reeza — Fri, 30 Jun 2017 17:06:14 GMT

HBOUND()

LBOUND()

@MeganE wrote:

Frankly, either there's a way to let sas determine the upper bound array name or there isn't. Me posting code isn't going to change whether or not someone knows if that's even possible. And i did post the array line. THe one line i needed help with.

You can short cut your variable list as well if you have a common prefix:

array myVars(*) Start: ;

If you're trying to automate the declaration of a macro that's going to be difficult, you need to precalculate the number, into a macro variable and work with that. The other option is to use PROC TRANSPOSE and then you don't need to know any of that. If you have multiple variables you'll need multiple transposes and a merge.

Re: Unknown upper bound array in listing variables

ballardw — Fri, 30 Jun 2017 17:19:45 GMT

@MeganE wrote:

I know the upper bound can be determined before hand, i said that in my initial post. What i also said is that because i'm working with millions of records, i'd rather not add to the processing time.

It looks like your option only allows me to compare two dates. I have multiple dates per id that i would like to see all on one line.

If those dates are not on the same record than an array, at least at the begining, is not going to work as arrays only exist for variables on a single record. What you will need to do is to transpose the data so those dates are one a single row

Something like this:

data example;
   do id = 1 to 3;
      do j= 1 to 5;
         date = today() + id +j; /* just to have some variety in the dates*/
         output;
      end;
   end;
   format date mmddyy10.;
run;

proc transpose data=example out=extrans
   prefix=start ;
by id;
var date;
run;

However with "millions" of records depending on how many ids and dates are concerned this might be a longish program.

And if you are looking at end dates you would need to make two separate transproses and then merge the two transposed sets to get both variables on the same row.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 17:21:11 GMT

So right now, sas doesn't have the ability to determine an upper bound array when using an array to create new variables. That's what i'm hearing.

Thanks

Re: Unknown upper bound array in listing variables

ballardw — Fri, 30 Jun 2017 18:01:08 GMT

@MeganE wrote:

So right now, sas doesn't have the ability to determine an upper bound array when using an array to create new variables. That's what i'm hearing.

Thanks

Not quite because what you are appently attempting to do seems not to be an array at all.

You can create an array of missing values with

array x (25); Which will create variables x1 through x25.

Optionally you can supply initial values BUT you have to explicitly indicate how many variables you are creating so that SAS can set aside storage.

If you want to use existing variables then you have to indicate the names in one way or another

Explicit list of ALREADY EXISTING variables

array y varname thisvar other;

or numbered list of existing variables:

array z var1 - var35;

or a list of EXISTING variables that start with similar characters

array a ext: mnt: ; would place all variables that start with EXT or MNT into the array

or a list of contiguous variables that are in column order in a data set:

array b varincolum5 -- anothervarincolum15; note the two - that is the contiguous column list definer.

you can mix the approaches but all of those reference variables in the same observation (or same row ).

But you need to provide something to build an array with a fixed number of elements.

Your description, since you haven't provided even a 10 row example of data an what you are trying to actually do, sounds like you want to look at elements across rows. Which is the perview of PROC IML and the matrix language or reshaping the data so all of the values you want are on a single row. An array cannot ever in a data step look across rows.

Some languages include structures called a Lists or stacks that may behave as you want but those both require an entirely differentl approach to data storage.

Re: Unknown upper bound array in listing variables

MeganE — Fri, 30 Jun 2017 18:04:37 GMT

Thanks, but, again, i do not want to use existing variables. I am trying to create new variables.

I just talked to sas support, and they have confirmed that sas cannot determine the upperbound of an array that is being used to create BRAND NEW VARIABLES during the process. It has to be figured out beforehand.

Re: Unknown upper bound array in listing variables

Quentin — Fri, 30 Jun 2017 18:30:09 GMT

@MeganE wrote:

I just talked to sas support, and they have confirmed that sas cannot determine the upperbound of an array that is being used to create BRAND NEW VARIABLES during the process. It has to be figured out beforehand.

Sounds right. Rember that the ARRAY statement does its work at COMPILE time. If you are using an array statement to create new variables (at compile time), you need to tell the array statement how many variables to create.

There have been times I've hoped there might be a syntax like:

data want;
  x1=1; x2=2; x3=3;

  array x{*} x: ;

  array y{dim(x)} ; *pseudocode;

run;

I think that is something like what you might have been hoping for? But that doesn't work because the dim() function works at execution time. Even if it did work at compile time, The only allowed arguments to specify the number of elements in an array are an integer or *.

Basically, I think you are forced to pre-compute the number of variables you want in the array in SAS, because there is no way for SAS to compute it. (There are often ways to do this computation dynamically so that it isn't hardcoded.)

Re: Unknown upper bound array in listing variables

Tom — Fri, 30 Jun 2017 21:33:46 GMT

@MeganE wrote:

So right now, sas doesn't have the ability to determine an upper bound array when using an array to create new variables. That's what i'm hearing.

Thanks

That is not true at all. SAS can always determine how many variables you have specified for an array.

What you are asking for is how to generate a program that will convert an unknown number of OBERVATIONS into variables.

So yes in that limited situation SAS cannot figure out what would be an ideal upper bound for your array. You will need to tell it what upper bound to use. (or lower bound for that matter).

You could calculate an estimate. Perhaps based on how many observations are in the source data.

proc sql noprint; select count(*) into :upper trimmed from have ; quit;
data want ;
   set have ;
   array start (&upper) ;
   ...
run;

Or perhaps you are doing BY variable processing so you only need enough for the largest group.

proc sql noprint; 
  select max(nobs) into :upper trimmed 
  from (select byvar,count(*) as nobs from have group by byvar)
  ; 
quit;
data want ;
  do _n_=1 by 1 until (last.byvar);
     set have ;
     by byvar;
     array start (&upper) ;
     start(_n_)= myvar ;
  end;
run;

Or you could just pick an upperbound that is larger than you expect to need. You can even make the code smoothly deal with the sitiation where it is not large enough. For example by just starting another output observation.

data want ;
  do _n_=1 to 200 until (last.byvar);
    set have ;
    by byvar;
    array start (200) ;
    start(_n_)= myvar ;
  end;
run;