Tax loss for the past three years

[Agent1592]

I am trying to use the SAS SQL procedure to download some data from a table.

I have a table that has a column with a variable TLCF (Tax Loss).

How can I create a SQL Proc that creates an indicator variable when TLCF>0 in any of the previous years.

I know the case for the current year.

I use the command:

case when TLCF>0 then 1 else 0 end asTLCF.

What is the SQL code to tell that I want this a 1 if in any of the previous three years there was a Loss.

[Shmuel]

Try next code again:

proc sql;

        create table one as 

        select distinct id, case when TLCF>0 then 1 else 0 end asTLCF_flag

        from have(where =( year between year(today()) -1 and year(today()) -3))

    ;

        create table want as

        select a.* ,

                  max(b.TLCF_flag) as TLCF

        from have(drop=TLCF) as a

        left join one as b

        on a.id  = b.id

        group by id

        order by id year;

quit;

[Agent1592]

Sorry it doesn't seem to be working:

ERROR: Unable to parse data set options.

ERROR 79-322: Expecting a ).

ERROR: The variable TLCF in the DROP, KEEP, or RENAME list has never been referenced.

ERROR: File WORK.ONE.DATA does not exist.

[Reeza]

---

@Agent1592 wrote:

Sorry it doesn't seem to be working:

ERROR: Unable to parse data set options.

ERROR 79-322: Expecting a ).

ERROR: The variable TLCF in the DROP, KEEP, or RENAME list has never been referenced.

ERROR: File WORK.ONE.DATA does not exist.

---

What's it?

Which solution did you try that didn't work. I don't think the SQL solution answers your question. 

[Reeza]

SQL doesn't allow row type functions so accessing a previous row or later row is difficult. 

If you're connecting to a DB and can use SQL pass through then you can look at SQL cursor logic that's native to your DB. 

Use a data step, and possibly a DoW loop. 

Can you post sample data that includes your input and expected output? 

[mkeintz · Accepted solution]

If your data is in a SAS dataset and is grouped by CUSIP, and sorted by year within cusip, then it's fast and simple in a data step:

data want;

  set have;

  by cusip notsorted;

  new_dummy= tlcf or ifn(lag1(cusip)=cusip,lag1(tlcf),0) or ifn(lag2(cusip)=cusip,lag2(tlcf),0);

run;

Mark 

[Reeza]

Depending on what you want as a final answer this could be as simple as very general data step using first/last and retain.  You should post sample data as to what you'd like as output and what your data looks like. 

data want;
set have;
by id;
retain flag;

if first.id then flag=0;
if tclf>0 then flag=1;

run;

[Agent1592]

Have table:

ID	fyear	TAXLOSS
1	2000	0
1	2001	54
1	2002
1	2003
1	2004	60.703
1	2005	39.028
1	2006
1	2007
1	2008	0
1	2009	0
1	2010	0
1	2011	3
1	2012	0
1	2013	0
1	2014	4
1	2015	0
2	2000	0
2	2001	6.134
2	2002	6.134
2	2003
2	1992	0
2	1993
2	1994

Want table:

ID	fyear	TAXLOSSInd
1	2000	0
1	2001	0
1	2002	1
1	2003	1
1	2004	1
1	2005	1
1	2006	1
1	2007	1
1	2008	1
1	2009	0
1	2010	0
1	2011	1
1	2012	1
1	2013	1
1	2014	1
1	2015	1
2	2000	0
2	2001	1
2	2002	1
2	2003	1
2	1992	1
2	1993	1
2	1994	0

Basically I would like an indicator variable equal to 1 if TAXLOSS>0 in any of the previous 3 years. 

[Reeza]

Data step solution. 

data want;
array p{0:2} _temporary_;
set have; by if fyear;
if first.id then call missing(of p{*});
p{mod(_n_,3)} = taxloss;
Flag = if min(of p{*}) > 0;

run;

SQL

proc SQL;

 create table want as 

select a.* , case when sum(b.taxloss, c.taxloss,  d.taxloss) > 0 then 1

           Else 0 end as flag

from have as a,

left join have as b

in a.id=b.id and a.year-1=b.year

left join have as c

on a.id =c.id and a.year-2=b.year

/*and once more for year-3*/

;

quit;

[Agent1592]

proc SQL;

create table want (drop=DUMMY1 DUMMY2 DUMMY3) as

select a.* , case when b.TAXLOSS in (., 0) then 0 else 1 end as DUMMY1, case when c.TAXLOSS in (., 0) then 0 else 1 end as DUMMY2,

case when d.TAXLOSS in (., 0) then 0 else 1 end as DUMMY3,

max(calculated DUMMY1, calculated DUMMY2, calculated DUMMY3) as TAXLOSS_DUMMY

from have as a

inner join have as b

on a.ID=b.ID and a.FYEAR=b.FYEAR+1

inner join have as c

on a.ID=c.ID and a.FYEAR=c.FYEAR+2

inner join have as d

on a.ID=d.ID and a.FYEAR=d.FYEAR+3; quit;