topic Re: Multiple call symput in SAS Programming

Multiple call symput

jo1 — Thu, 22 Jun 2017 22:34:54 GMT

Hi,

I have a large amount of customer data that is a value for each half hour period of the day. I have the data table set up with Date; Time; and then each column is a new customer. In case it helps what I'm doing is looking at a combination of the customers eg 2 at time, 5, 10, 100 etc to work out at what level the diversity of the single customer is lost - ie as the group size increases my time of max demand will stablise.

I have come up with a way to sample the customers for a particular level (eg this code sums the values of 10 random customers for each time and date period) but I'm sure there must be a shorter way to code this for say when I want to do a level of 100.

So basically I'm looking for a shorter way to create the sequence of random numbers as variables to eliminate the length of the first data step and then also a shorter way to write the R1= in the second datastep.

/* SAS macro that duplicates teh Excel RANDBETWEEN function*/

%macro RandBetween(min, max);

(&min + floor((1+&max-&min)*rand("uniform")))

%mend;

/*Randomly select the columns for the calculation */

data _null_;

set random;

call symput("rand1",%RandBetween(1,4747));

call symput("rand2",%RandBetween(1,4747));

call symput("rand3",%RandBetween(1,4747));

call symput("rand4",%RandBetween(1,4747));

call symput("rand5",%RandBetween(1,4747));

call symput("rand6",%RandBetween(1,4747));

call symput("rand7",%RandBetween(1,4747));

call symput("rand8",%RandBetween(1,4747));

call symput("rand9",%RandBetween(1,4747));

call symput("rand10",%RandBetween(1,4747));

run;

/*Sums the 10 random columns*/

data Sample;

set Cust_T;

array Col[4747] col1-col4747; /* # columns = # customers */

R1=Sum(Col(&Rand1)+Col(&Rand2)+Col(&Rand3)+Col(&Rand4)+Col(&Rand5)

+Col(&Rand6)+Col(&Rand7)+Col(&Rand8)+Col(&Rand9)+Col(&Rand10));

DROP col1-col4747 n _Name_;

run;

Re: Multiple call symput

Reeza — Fri, 23 Jun 2017 02:50:04 GMT

I feel like it would save you a lot of time to just transpose the data. Then you can use PROC SURVEYSELECT and no macro variables.

CALL RANPERK may be another option, without knowing fully, it's hard to say. If you can change the order of the variables without consequence this is a good option. Remember that it doesn't have to be permanent 🙂

A third option would be to create another array with 10 elements and create random numbers there, only once when _n_=1. Then use those with your loop, no need for macro variables here at all. Untested sample below:

%let window=10;
data want;
set have;

array myRand(*) _temporary_ rand1-rand&window;
array other(*) other1-other4564;

if _n_ = 1 then do i=1 to dim(myRand);
    myRand(i) = rand(******);
end;

total = 0;

do i=1 to dim(myRand);
total = sum(total, other(myRand(i)));
end;

run;

Re: Multiple call symput

ChrisNZ — Fri, 23 Jun 2017 03:56:37 GMT

Like this?

%macro RandBetween(min, max);
(&min + floor((1+&max-&min)*rand("uniform")))
%mend;

%let nb_col=10;

data SAMPLE;
  set CUST_T;
  array COL[4747] COL1-COL4747; /* # columns = # customers */
  do I=1 to &nb_col.;
    R1=sum(R1,COL[%RandBetween(1,4747)]);
  end;
  drop COL1-COL4747 I N _NAME_;
run;