topic Re: Dummy variables for years in SAS Programming

Dummy variables for years

niloo — Fri, 11 Dec 2015 03:21:18 GMT

Hi everyone,

I have a sas program that i have been using and now I need to add dummy variables for years and industry to my variables and then use these dummy variables in regressions. So I was thinking of first adding a column called year:

year=year(Andate);
run;

and then the dummy variables :

zero=0;
one=0;
two=0;
three=0;
four=0;
five=0;
six=0;
seven=0;
eight=0;
nine=0;
ten=0;
eleven=0;
twelve=0;
thirteen=0;
fourteen=0;
if year=2000 then zero=1;
if year=2001 then one=1;
if year=2002 then two=1;
if year=2003 then three=1;
if year=2004 then four=1;
if year=2005 then five=1;
if year=2006 then six=1;
if year=2007 then seven=1;
if year=2008 then eight=1;
if year=2009 then nine=1;
if year=2010 then ten=1;
if year=2011 then eleven=1;
if year=2012 then twelve=1;
if year=2013 then thirteen=1;
if year=2014 then fourteen=1;

Am I correct?Also my professor insists that if the number of dummies is equal to the number of treatments, the regression will suffer from exact multicolinearity, so I have to have 13 dummies for year. If I only create 13 dummies for year (for example from 2000 to 2013) how can I run a regression on my dependant variable (carwindow1) and year 2014?

Here is my other dummies that I have been using before: (only the dummy part )

data a;
set ev.final2CarAn;
cs=.;
if ConsiStr='SHARES' then CS=1;
if ConsiStr='CASHO' then CS=2;
if ConsiStr='HYBRID' then CS=3;
if ConsiStr='OTHER' then CS=4;

ratio=.;
if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;
proc sort;
by type;
run;

data ev.b;
set a;

h=0;
v=0;
c=0;

if type=1 then h=1;
if type=2 then v=1;
if type=3 then c=1;

ps=0;
pk=0;
ph=0;
po=0;

if cs=1 then ps=1;
if cs=2 then pk=1;
if cs=3 then ph=1;
if cs=4 then po=1;

la=0;
li=0;
if local=1 then la=1;
if local=0 then li=1;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);

run;

And then I used the following statements to run regressions on each variable and my main variable (carwindow1):

proc reg data=ev.b outest= estb tableout ADJRSQ;
model CARwindow1= h ;
run;
data est2 (keep= _type_ _ADJRSQ_ _RMSE_ intercept h v c ps pk ph po la li lnTMV lnTEq lnTEn ratio AEQW);
set estb;
run;
data _null_;
proc datasets force nolist;
append base=ev.result1
data=est2;
run;

Thank you,

Niloo

Re: Dummy variables for years

Reeza — Fri, 11 Dec 2015 03:38:53 GMT

You need N-1 indicators. You actually have 15 years so you need 14 indicator variables, the one you leave out is your reference level - what you compare things to.

Your sample code doesn't correctly account for the differing levels-rationally shares vs other having a difference of 3 doesn't make sense.

You may find it helpful to look at PROC GLM and the CLASS statement, noting the reference level options.

Re: Dummy variables for years

niloo — Fri, 11 Dec 2015 03:46:04 GMT

Hi Reeza,

Thank you for your response. I do not understand what you mean by having a difference of 3.

What about my older statements? are they wron too?

for type of payment( cash, stock, hybrid, other) we have 4 dummy variable:

ps=0;

pk=0;

ph=0;

po=0;

if cs=1 then ps=1;

if cs=2 then pk=1;

if cs=3 then ph=1;

if cs=4 then po=1;

Thanks,

Niloo

Re: Dummy variables for years

PGStats — Fri, 11 Dec 2015 04:27:35 GMT

If you absolutely need to use proc reg then you could save some work by running proc glmmod to create the dummy variables. It would look like this:

data a;
set ev.final2CarAn;
if amval4wp ne 0 and amval4wp ne . then
	ratio=tmval4wp/amval4wp;
lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
year = year(Andate);
run;

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type local year;
model carwindow1 = ratio lnTEq lnTEn lnTMV lnAMV ConsiStr type local year / noint;
run;

/* Check the meaning of col1, col2, etc. variables in the new dataset */
proc print data=parm; run;

/* If you want, rename the variables created by GLMMOD */
data c;
set b;
rename col1=ratio col2=lnTEq ....;
run;

proc reg data=c;
model carwindow1 = ....;
run;

Re: Dummy variables for years

PaigeMiller — Fri, 11 Dec 2015 13:42:41 GMT

PROC GLMMOD should be more widely known. Very useful procedure!

Re: Dummy variables for years

PGStats — Fri, 11 Dec 2015 16:14:53 GMT

Yes, I just wish it could generate more meaningful variable names, now that the limit has been increased to 32 characters.

Re: Dummy variables for years

niloo — Mon, 14 Dec 2015 04:54:45 GMT

Hi PGStats,

Thank you very much for your solution. It is much more efficient than my previous statements. I revised my program and have 3 question:

1) Shall I add the variables that I want to include in the regression in this part?

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type year;
model Acar1 = ratio lnTEq lnTEn lnTMV lnAMV lnSIZE ConsiStr type year (other variables) / noint;
run;

2) I have 15 years of data (2000-2014) and 15 dummy variables. Isn't this going to cause a multicolinearity problem?

3) If I want to creat another set of dummy variables for industry can I add this statement:

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);

Industry= TMaCode;
run;

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type year Industry;

Here is how my statemets look like now:

libname ev 'C:\Regression';
run;

***Majority All data set- Regression for Acquirors, CAR (-1, +1), announcment date ********/;

data final2;
set ev.final2(keep= tdscd ADSCD Aname Tname AnDate Wdate Acar1 Acar5 Tcar1 Tcar5 TobinQ Debtratio HOSTATASS AMVal4wp TMVal4wp EqValAn EnValAn ConsiStr TNation type AEQW);
proc sort;
by adscd andate;
run;

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);
run;

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type year;
model Acar1 = ratio lnTEq lnTEn lnTMV lnAMV lnSIZE ConsiStr type year / noint;
run;
proc print data=parm; run;

data c;
set b;
rename col1=ratio col2=lnTEq col3=lnTEn col4=lnTMV col5=lnAMV col6=lnSIZE col7=cash col8=hybrid col9=other col10=shares
col11=unknown col12=horizental col13=vertical col14=conglomerate col15=year0 col16=year1 col17=year2 col18=year3 col19=year4
col20=year5 col21=year6 col22=year7 col24=year9 col25=year10 col26=year11 col27=year12 col28=year13 col29=year14;
run;

proc reg data=c;
model Acar1 = lnSIZE;
run;

Thank you very much,

Niloo

Re: Dummy variables for years

PaigeMiller — Mon, 14 Dec 2015 19:43:49 GMT

@niloo wrote:

Hi PGStats,

Thank you very much for your solution. It is much more efficient than my previous statements. I revised my program and have 3 question:

1) Shall I add the variables that I want to include in the regression in this part?

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type year;
model Acar1 = ratio lnTEq lnTEn lnTMV lnAMV lnSIZE ConsiStr type year (other variables) / noint;
run;

Yes, if you want the other variables in the regression. However, this seems needlessly complicated, you are planning to run PROC REG so you need DUMMY variables; but if you run the analysis in PROC GLM, you don't have to create the dummy variables yourself, PROC GLM does that for you internally.

2) I have 15 years of data (2000-2014) and 15 dummy variables. Isn't this going to cause a multicolinearity problem?

Again, this is all taken care of internally in PROC GLM, so why use PROC REG? Anyway, the NOINT option eliminates the multicollineary issue.

3) If I want to creat another set of dummy variables for industry can I add this statement:

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);

Industry= TMaCode;
run;

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type year Industry;

Here is how my statemets look like now:

libname ev 'C:\Regression';
run;

***Majority All data set- Regression for Acquirors, CAR (-1, +1), announcment date ********/;

data final2;
set ev.final2(keep= tdscd ADSCD Aname Tname AnDate Wdate Acar1 Acar5 Tcar1 Tcar5 TobinQ Debtratio HOSTATASS AMVal4wp TMVal4wp EqValAn EnValAn ConsiStr TNation type AEQW);
proc sort;
by adscd andate;
run;

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);
run;

proc glmmod data=a outdesign=b outparm=parm;
class ConsiStr type year;
model Acar1 = ratio lnTEq lnTEn lnTMV lnAMV lnSIZE ConsiStr type year / noint;
run;
proc print data=parm; run;

data c;
set b;
rename col1=ratio col2=lnTEq col3=lnTEn col4=lnTMV col5=lnAMV col6=lnSIZE col7=cash col8=hybrid col9=other col10=shares
col11=unknown col12=horizental col13=vertical col14=conglomerate col15=year0 col16=year1 col17=year2 col18=year3 col19=year4
col20=year5 col21=year6 col22=year7 col24=year9 col25=year10 col26=year11 col27=year12 col28=year13 col29=year14;
run;

proc reg data=c;
model Acar1 = lnSIZE;
run;

PROC GLM, PROC GLM, PROC GLM! Stop going through all this effort to create dummy variables.

Re: Dummy variables for years

niloo — Tue, 15 Dec 2015 05:43:18 GMT

Dear Paige Miller,

Thank you for your response. So I will not use a PROC REG. But my PROC GLM does not lead to an output page with regression results. I need to run a regression on my dependent variable (ACARs1) and each on my dependent variable and then in the next level on my dependent variable and combinations of my independent variables. Only when I delete the outdesign and outparm and limit the independent variables to one I get the output window with regression results. How can I fix my statements?

Here is how my statements look like:

libname ev 'C:\Regression';
run;

***Majority All data set- Regression for Acquirors, CAR (-1, +1), announcment date ********/;
data final2;
set ev.final2(keep= tdscd ADSCD Aname Tname AnDate Wdate Acar1 Acar5 Tcar1 Tcar5 TobinQ ADebtratio DEBTRT HOSTATASS AMVal4wp TMVal4wp
EqValAn EnValAn ConsiStr TNation type AEQW);
proc sort;
by adscd andate;
run;

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);
Industry= TMaCode;
run;

/*this does not give me an output window*/

proc glmmod data=a outdesign=b outparm=parm ;
class ConsiStr type year Industry;
model Acar1 = ratio lnTEn lnSIZE ConsiStr type year ADebtratio TobinQ DEBTRT Industry AEQW / noint;
run;
proc print data=parm; run;

/*this one gives me an output window*/

proc glm data=a ;
class year;
model Acar1 = year / noint;
run;

Thank you,

Niloo

Re: Dummy variables for years

PaigeMiller — Tue, 15 Dec 2015 13:12:01 GMT

But my PROC GLM does not lead to an output page with regression results.

Sure it does. PROC GLM performs a regression on your data. (And you need a QUIT; statement at the end of PROC GLM)

The results are written to the output window (unless you have somehow turned that off).

And then you can take all the PROC GLMMOD statements out of your code.

Re: Dummy variables for years

niloo — Wed, 16 Dec 2015 22:30:04 GMT

Dear PaigeMiller,

Thank you for your response. I chaned my statements to following and I am trying to export the results into an excel sheet but the ODS statement that I have exports each table to a seperate sheet. how can I have al the results in one sheet and not in table format?

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);
Industry= TMaCode;
run;

ODS TAGSETS.EXCELXP
file='C:\Users\esy\Desktop\niloofar\DePaul Research\M&AJAPAN\Majority\Results\regression.xlsx'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = year / noint;
QUIT;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = ratio / noint;
QUIT;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = TobinQ/ noint;
QUIT;
run;
(I will run the same statment for all my variavles individually, and then for a combination of variables which had meaningful relationships with the dependent variable):
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = ADebtratio ratio / noint;
QUIT;
run;

Thank you,

Niloo

Re: Dummy variables for years

Reeza — Wed, 16 Dec 2015 22:32:52 GMT

Look at the sheet_interval option - the value you're looking for is none.

I don't know what you mean by not in table format.

Re: Dummy variables for years

Reeza — Wed, 16 Dec 2015 22:34:56 GMT

PS. I think your original question has been answered. You should mark this question as answered and start new ones if you have further questions.

Re: Dummy variables for year

niloo — Thu, 17 Dec 2015 17:50:47 GMT

Hi again,

I have a question about the dummy variables created by Proc GLM. Variable "type" showes the type of payment (Cash, stock, Hybrid, Unknown) and variable "ConsiStr" discribes the type of deal(horizental, vertical, conglomerate). I need to run a regression on my dependent variable (ACAR1) and "cash" type of payment (not the whole "type" variable) and on ACAR1 and "conglomerate" dales( not the ConsiStr variable itself. But I cannot do it using my current PROC GLM statements. Is there a way I can do this using Proc GLM?

Here is what I currently have:

libname ev 'C:\Regression';
run;

data final2;
set ev.final2(keep= tdscd ADSCD Aname Tname AnDate Wdate Acar1 Acar5 Tcar1 Tcar5 TobinQ ADebtratio DEBTRT HOSTATASS AMVal4wp TMVal4wp
EqValAn EnValAn ConsiStr TNation type AEQW);
proc sort;
by adscd andate;
run;

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);
Industry= AMaCode;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = year / noint;
QUIT;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = Industry / noint;
QUIT;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = AEQW/ noint;
QUIT;
run;

.... and the rest of the variables

ODS TAGSETS.EXCELXP
file='C:\Users\esy\Desktop\niloofar\DePaul Research\M&AJAPAN\Majority\Results\regression.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' sheet_interval='NONE' );

Thanks,

Niloo

Re: Dummy variables for year

PGStats — Thu, 17 Dec 2015 18:55:27 GMT

You can restrict your regression analysis to a subset of cases with the where statement

proc glm data=a;
where type="cash";
class ConsiStr year Industry;
model Acar1 = year / noint;
run;
QUIT;

Note: the quit statement goes after the run statement.

Re: Dummy variables for year

niloo — Thu, 17 Dec 2015 19:13:48 GMT

Thanks PG, I tried it but I got this error:

74 proc glm data=a;
75 where type="cash";
ERROR: WHERE clause operator requires compatible variables.
76 class ConsiStr year Industry;
77 model Acar1 = year / noint;
78 run;

WARNING: RUN statement ignored due to previous errors. Submit QUIT; to terminate the procedure.
79 QUIT;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE GLM used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

Shall I run the PROC GLMMOD before my PROC GLM statements?

Re: Dummy variables for year

PGStats — Thu, 17 Dec 2015 19:44:33 GMT

The message "WHERE clause operator requires compatible variables" means that type must be numeric and "cash" is only the formatted representation of its value. You must either replace "cash" with a number or change the where condition to

where put(type, yourTypeFormat.) = "cash";

Re: Dummy variables for year

niloo — Thu, 17 Dec 2015 20:34:58 GMT

Sorry I made a mistake. In my data base "type" is numeric: 1for horizental deals, 2 for vertical and 3 for conglomerate.(so there is no horizental, vertical, conglomerate under type variable only 1 and 2 and3. "ConsiStr" is a character variable: Cash, stock, hybrid, unknown.

I changed the statements to:

proc glm data=a;
where type="3";
class ConsiStr year Industry;
model Acar1 = year / noint;
run;
QUIT;
proc glm data=a;
where put (ConsiStr,$char6.)="cash";
class ConsiStr type year Industry;
model Acar1 = ConsiStr / noint;
run;
QUIT;

and the error is still:

46 proc glm data=a;
147 where type="3";
ERROR: WHERE clause operator requires compatible variables.
148 class ConsiStr year Industry;
149 model Acar1 = year / noint;
150 run;

WARNING: RUN statement ignored due to previous errors. Submit QUIT; to terminate the procedure.
151 QUIT;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE GLM used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

152 proc glm data=a;
153 where put (ConsiStr,$char6.)="cash";
154 class ConsiStr type year Industry;
155 model Acar1 = ConsiStr / noint;
156 run;

NOTE: No observations were selected from data set WORK.A.
NOTE: PROCEDURE GLM used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

157 QUIT;

thanks,

Niloo

Re: Dummy variables for year

PGStats — Thu, 17 Dec 2015 21:23:12 GMT

So, I guess the condition should be

where ConsiStr = "cash";

/* or */

where ConsiStr = "Cash";

/* or, to play safe */

where UPCASE(ConsiStr) = "CASH";

Re: Dummy variables for year

niloo — Thu, 17 Dec 2015 21:57:52 GMT

Thank you soooooo much PG it worked. i used the third one.

However the type variable still gives me errors:

proc glm data=a;
where UPCASE(type)="3";
class type ConsiStr year Industry;
model Acar1 = year / noint;
run;
QUIT;

error:

182 proc glm data=a;
183 where UPCASE(type)="3";
ERROR: Function UPCASE requires a character expression as argument 1.
184 class type ConsiStr year Industry;
185 model Acar1 = year / noint;
186 run;

WARNING: RUN statement ignored due to previous errors. Submit QUIT; to terminate the procedure.
187 QUIT;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE GLM used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds