DATA Step, Macro, Functions and more

Tax loss for the past three years

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Tax loss for the past three years

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.

 


Accepted Solutions
Solution
‎11-25-2016 02:26 AM
Valued Guide
Posts: 797

Re: Tax loss for the past three years

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 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,374

Re: Tax loss for the past three years

[ Edited ]

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;

Contributor
Posts: 29

Re: Tax loss for the past three years

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.

Super User
Posts: 17,819

Re: Tax loss for the past three years


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. 

Super User
Posts: 17,819

Re: Tax loss for the past three years

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? 

Solution
‎11-25-2016 02:26 AM
Valued Guide
Posts: 797

Re: Tax loss for the past three years

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 

Super User
Posts: 17,819

Re: Tax loss for the past three years

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;
Contributor
Posts: 29

Re: Tax loss for the past three years

Have table:

IDfyearTAXLOSS
120000
1200154
12002 
12003 
1200460.703
1200539.028
12006 
12007 
120080
120090
120100
120113
120120
120130
120144
120150
220000
220016.134
220026.134
22003 
219920
21993 
21994 

 

 

Want table:

IDfyearTAXLOSSInd
120000
120010
120021
120031
120041
120051
120061
120071
120081
120090
120100
120111
120121
120131
120141
120151
220000
220011
220021
220031
219921
219931
219940

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

Super User
Posts: 17,819

Re: Tax loss for the past three years

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;

Contributor
Posts: 29

Re: Tax loss for the past three years

[ Edited ]

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 236 views
  • 1 like
  • 4 in conversation