do loop and macro.

[aaronsterri]

Hi, i have a data set that has millions of obs.

the variables are:

date, volume, companyname, pseudovolume

the issue is that i only have volume until day 365 and have to roll the volume to get the next one starting the 366th date.

so, i would first sort the dataset by companyname and date

then i wanted to write a sas macro doing the following

if date is the 366th date, volume=(pseudovolume-psedovolume_lag)+volume at day 1.

if date is the 367th date, volume=(pseudovolume-psedovolume_lag)+volume at day 2.

if date is the 368th date, volume=(pseudovolume-psedovolume_lag)+volume at day 3.

i have to do this on and on until 13000th date.

is there anyway to do this using sas macro ? thanks in advance

[Astounding]

It's difficult to see what this question has to do with either macro language or do loops. Perhaps you should begin by answering a few questions.

Could there be multiple observations for the same company/date combination?

How do you know whether it is day 1, day 2, etc.?

If there are 4 days between one date and the next, how do you calculate the day?

[aaronsterri]

thank you so much for your note.

sorry i forgot to let you know that for each company, i have unique firm day observations. 

so for example, company a can have the first date as 1/1/2008 and company b can have the first date as 4/15/2010.

i just do a count so that i know for each company what the first day is.

[Astounding]

It sounds like (please confirm) for each COMPANY:

DAY 1 is the earliest DATE for that COMPANY.

DAY 2 is the next earliest DATE for that COMPANY, even if it falls 5 days after the earliest DATE.

[PaigeMiller]

if date is the 366th date, volume=(pseudovolume-psedovolume_lag)+volume at day 1.

I don't think you have explained the desired analysis in any level of completeness so that we can provide an answer

So help me understand this ...

Question 1: what is psedovolume_lag?

Question 2: What happens on day 731? Is it:

• if date is the 731th date, volume=(pseudovolume-psedovolume_lag)+volume at day 1
  or is it
• if date is the 731th date, volume=(pseudovolume-psedovolume_lag)+volume at day 366
  or is it
• something else?

Question 3: What do you do about leap years?

Question 4: what is the desired output for days 1 to 365?

[mkeintz]

You have specified a formula for volume as 

     volume(t)   = volume(1)  + pseudovolume(t) - pseudovolume(t-1)

regardless of whether t is the number of days since day 1,  of the number of records since record 1, it's a stretch to label this a rolling value.  You're specifying a formula that (for a series beginning in 01jan1990, with 13,000 consecutive dates), means this:

    volume(05AUG2025) = volume(01JAN1990) + pseudovolume(05AUG2025) - pseudovolume(04AUG2025)

Is this what you really want?