topic Re: Tax loss for the past three years in SAS Programming

Tax loss for the past three years

Agent1592 — Fri, 25 Nov 2016 00:18:45 GMT

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.

Re: Tax loss for the past three years

Shmuel — Fri, 25 Nov 2016 05:26:32 GMT

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;

Re: Tax loss for the past three years

Reeza — Fri, 25 Nov 2016 01:17:01 GMT

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?

Re: Tax loss for the past three years

mkeintz — Fri, 25 Nov 2016 04:01:00 GMT

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

Re: Tax loss for the past three years

Agent1592 — Fri, 25 Nov 2016 05:20:40 GMT

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.

Re: Tax loss for the past three years

Reeza — Fri, 25 Nov 2016 05:29:11 GMT

@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.

Re: Tax loss for the past three years

Reeza — Fri, 25 Nov 2016 05:31:49 GMT

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;

Re: Tax loss for the past three years

Agent1592 — Fri, 25 Nov 2016 05:48:53 GMT

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.

Re: Tax loss for the past three years

Reeza — Fri, 25 Nov 2016 06:03:37 GMT

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;

Re: Tax loss for the past three years

Agent1592 — Fri, 25 Nov 2016 20:48:05 GMT

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;