BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

9 REPLIES 9
Shmuel
Garnet | Level 18

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
Pyrite | Level 9

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
Super User

@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
Super User

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
PROC Star

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 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reeza
Super User

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
Pyrite | Level 9

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. 

Reeza
Super User

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
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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