topic Re: 3 Versions of Dependent Variable in Same Dataset in SAS Programming

3 Versions of Dependent Variable in Same Dataset

jjadall1 — Tue, 17 Oct 2017 01:56:25 GMT

Hello SAS Support Communities,

Please see the code below. For your information, cik in the code is a unique identifier for each company (e.g., Lowe's, Home Depot, etc.) Let me give you an example of what I'm looking for. Let's say a company has a 12/31 fiscal year-end. There's a restatement that covers January 1, 2009 (rpb in the code below) through June 30, 2012 (rpe in the code below) that is disclosed on June 15, 2014.

I would like 3 versions of a categorical dependent variable in the same dataset as follows:

One with a categorical dummy variable = 1 if restatement_key (identifier of each restatement (January 1, 2009 through June 30, 2012 in my example)) is in year 1 of the restatement. That would be if first.restatement key then do, correct? In my example, the only categorical dependent variable with a value of 1 would be fyear = 2009.

One with a categorical dummy variable = 1 if fyear is between y1 and y2. In my example, the categorical dependent variable would have a value of 1 when fyear = 2009, 2010, 2011, and 2012.

One with a categorical dummy variable = 1 only in the year of disclosure. In my example, the categorical dependent variable would have a value of 1 when fyear = 2014.

data SAS_data.restatement1;

set SAS_data.restatement;

rpb = restated_period_begin;

rpe = restated_period_ended;

RstateDays = (Datdif (rpb, rpe, 'act/act'))+1;

y1 = year(restated_period_begin);

y2 = year(restated_period_ended);

y3 = year(disclosure_date);

run;

data SAS_data.restatement1a;

set SAS_data.restatement1;

If RstateDays LT 350 then delete;

run;

data SAS_data.restatement2;

set SAS_data.restatement1a;

drop rpb;

drop rpe;

run;

proc sort data=SAS_data.restatement2 out=SAS_data.restatement2a;

by cik;

run;

/*create observation for each year restated [include begin year and end year] */

data SAS_data.restatement3;

set SAS_data.restatement2a;

by cik;

if cik then do;

do fyear=y1 to y3;

output;

end;

run;

Please let me know how I can get the three dependent variables in the same dataset starting with the code I already have.

God bless, best regards, and thank you so much for your help,
Jadallah

Re: 3 Versions of Dependent Variable in Same Dataset

error_prone — Tue, 17 Oct 2017 03:04:40 GMT

Please post test-data and the expected result-dataset, making it a lot easier to understand your problem.

Re: 3 Versions of Dependent Variable in Same Dataset

jjadall1 — Tue, 17 Oct 2017 14:23:56 GMT

Hello,

The original dataset (restatement) is attached. After running some code, another dataset is attached (restatement4). The three dependent variables should have values of 1 with the following observations from the restatement4 dataset:

DV 1

DV 2

DV 3

Please let me know how I should code this.

God bless, best regards, and thanks a lot,

Jadallah

Re: 3 Versions of Dependent Variable in Same Dataset

jjadall1 — Tue, 17 Oct 2017 14:48:21 GMT

I got it to work:

data Sas_data.restatement5;
set SAS_data.restatement4;
if y1 = fyear then FO=1;
else FO=0;
run;

data SAS_data.restatement6;
set SAS_data.restatement5;
if y3 = fyear then AD=1;
else AD=0;
run;

data SAS_data.restatement7;
set SAS_data.restatement6;
if y1 le fyear le y2 then AO=1;
else AO=0;
run;

The first dataset (restatement5) is for DV1. The second dataset (restatement6) is for DV3. The third dataset (restatement7) is for DV2.

Re: 3 Versions of Dependent Variable in Same Dataset

jjadall1 — Wed, 18 Oct 2017 15:43:11 GMT

That code above works if you have 12/31 year-ends ONLY. If there are some fiscal year-ends, use the following:

data Etr.restatement1;
set Etr.restatement;
rpb = restated_period_begin;
rpe = restated_period_ended;
RstateDays = (Datdif (rpb, rpe, 'act/act'))+1;
if month(restated_period_begin) le fiscal_month_end then y1 = year(restated_period_begin);
else y1 = year(restated_period_begin) + 1;
if month(restated_period_ended) le fiscal_month_end then y2 = year(restated_period_ended);
else y2 = year(restated_period_ended) + 1;
if month(disclosure_date) le fiscal_month_end then y3 = year(disclosure_date);
else y3 = year(disclosure_date) + 1;
run;

data Etr.restatement1a;
set Etr.restatement1;
If RstateDays LT 350 then delete;
run;

data Etr.restatement2;
set Etr.restatement1a;
drop rpb;
drop rpe;
run;

proc sort data=Etr.restatement2 out=Etr.restatement2a;
by cik;
run;

/*create observation for each year restated [include begin year and end year] */
data Etr.restatement3;
set Etr.restatement2a;
by cik;
if cik then do;
do fyear=y1 to y3;
output;
end;
end;
run;

proc sort data= Etr.restatement3 out= Etr.restatement4 noduplicate;
by restatement_key cik fyear;
run;

data Etr.restatement5;
set Etr.restatement4;
if y1 = fyear then FO=1;
else FO=0;
run;

data Etr.restatement6;
set Etr.restatement5;
if y3 = fyear then AD=1;
else AD=0;
run;

data Etr.restatement7;
set Etr.restatement6;
if y1 le fyear le y2 then AO=1;
else AO=0;
run;