Weekday and hour number interaction variables

[MetricsKevin]

Hello,

I have scoured the internet to no avail. I'm hoping someone would be able to help.  I need to interact dummy variables for the day of the week and the hour of the day.  I really don't want to hard code 168 variables.  I have to do this pretty regularly so I feel like learning a loop based method to would really help.  I already have the weekday and hour dummies that are 1 if true.  I would like to create 168 interactions that have names like day1_he1 ... day7_he24 that = 1 when the specified day and specified hour are both 1.  I am using SAS eg with base 9.02.

I have a date variable and a variable called HE which is the hour of the day.  Here is what I have tried:

data work.sample_data;

set work.sample_data;

weekday = weekday(date);

array ar_day{*} day1-day7;
do j = 1 to 7;
     ar_day(j) = (weekday=j);
end;

array ar_he{*} he1-he24;
do k = 1 to 24;
    ar_he(k) =(he=k);
end;

do l = 1 to 7;
    do m = 1 to 24;
        day&l_he&m = day&l*he&m;
    end;
end;

run;

Ther error I receive is:

WARNING: Apparent symbolic reference L_HE not resolved.
WARNING: Apparent symbolic reference M not resolved.
WARNING: Apparent symbolic reference I not resolved.
WARNING: Apparent symbolic reference M not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

Any help is appreciated.

[ChrisBrooks]

In your final do loop you're putting an ampersand in front of L_HE, M and L which makes SAS interpret them as macro variables when in fact they're data step variables which is why you're getting the errors.

If you can post a sample of your input data and output data in the form of data step so we can easily re-create it we can more easily give you a solution.

[MetricsKevin]

Im having a hard time exporting my data in sas form.  I was able to get it into excel and have posted below.  Its just dates in date9. and he is a number 3.

Thank you

[Reeza]

Try PROC GLM with a CLASS statement instead. Create two variables, one that represents the weekday, using 1 to 7 and one that represents the hour, 0 to 23 or 1 to 24. 

class weekday hour;

model dependent = weekday*hour ;

[MetricsKevin]

My bosses would really prefer that I stick to proc reg.  

[Reeza]

1. Why did you create a new ID? I'm assuming you're the same person who posted about weekday variables yesterday?

2. What procedure are you using afterwards? As indicated in the original question, depending on the procedures you can sometimes use the CLASS statement to avoid all of this.

---

@MetricsKevin wrote:

Hello,

I have scoured the internet to no avail. I'm hoping someone would be able to help.  I need to interact dummy variables for the day of the week and the hour of the day.  I really don't want to hard code 168 variables.  I have to do this pretty regularly so I feel like learning a loop based method to would really help.  I already have the weekday and hour dummies that are 1 if true.  I would like to create 168 interactions that have names like day1_he1 ... day7_he24 that = 1 when the specified day and specified hour are both 1.  I am using SAS eg with base 9.02.

I have a date variable and a variable called HE which is the hour of the day.  Here is what I have tried:

data work.sample_data;

set work.sample_data;

weekday = weekday(date);

array ar_day{*} day1-day7;
do j = 1 to 7;
     ar_day(j) = (weekday=j);
end;

array ar_he{*} he1-he24;
do k = 1 to 24;
    ar_he(k) =(he=k);
end;

do l = 1 to 7;
    do m = 1 to 24;
        day&l_he&m = day&l*he&m;
    end;
end;

run;

Ther error I receive is:

WARNING: Apparent symbolic reference L_HE not resolved.
WARNING: Apparent symbolic reference M not resolved.
WARNING: Apparent symbolic reference I not resolved.
WARNING: Apparent symbolic reference M not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

Any help is appreciated.

---

[MetricsKevin]

This is the first time I have ever posted here.  I need these for proc reg later.  

[Astounding · Accepted solution]

This is the code causing the problem:

do l = 1 to 7;
    do m = 1 to 24;
        day&l_he&m = day&l*he&m;
    end;
end;

It you don't want to code all the assignment statements, macro language can do it for you.  This would be the replacement code:

%macro loop;

%local i m;

%do i = 1 %to 7;
    %do m = 1 %to 24;
        day&i._he&m = day&i.*he&m;
   %end;
%end;

%mend loop;

%loop

[MetricsKevin]

This was the trick.  I didn't know you could put macros in data steps, mind = blown.

[Astounding]

Remember, the macro language statements are not part of the DATA step.  Instead, they are generating 144 SAS language assignment statements, which become part of the DATA step.  If you run the program using this statement beforehand, you can observe that in the log:

options mprint;

[Ksharp]

You don't need to write hard code to get this design matrix . Look this:

http://blogs.sas.com/content/iml/2016/02/24/create-a-design-matrix-in-sas.html