topic Re: do loop and macro. in SAS Programming

do loop and macro.

aaronsterri — Sat, 26 Oct 2019 21:37:25 GMT

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

Re: do loop and macro.

Astounding — Sat, 26 Oct 2019 22:22:26 GMT

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?

Re: do loop and macro.

aaronsterri — Sat, 26 Oct 2019 22:30:11 GMT

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.

Re: do loop and macro.

Astounding — Sat, 26 Oct 2019 22:50:36 GMT

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.

Re: do loop and macro.

PaigeMiller — Sat, 26 Oct 2019 22:53:58 GMT

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?

Re: do loop and macro.

mkeintz — Sat, 26 Oct 2019 22:56:11 GMT

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?