BookmarkSubscribeRSS Feed
jo1
Obsidian | Level 7 jo1
Obsidian | Level 7

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;

2 REPLIES 2
Reeza
Super User

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;

ChrisNZ
Tourmaline | Level 20

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 1299 views
  • 1 like
  • 3 in conversation