Problem in retaining observations in sql

Reply
Contributor
Posts: 30

Problem in retaining observations in sql

We can retain the values by retain statement in sas but i have data in teradata so i have to use sql

sample as data and if my val<500 i have to go back to prev qtr for particular id and add up to get 500 and count how many i went back

pls help I created the qtrs values in a macro I want in sql

data is

id                 qtr            val

10181Q2007493
10311Q2007175
10331Q2007227
10351Q2007188
10391Q2007545
10182Q2007441
10312Q2007301
10332Q2007295
10352Q2007312
10392Q2007607
10183Q2007458
10313Q2007281
10333Q2007357
10353Q2007388
10393Q2007516
10184Q2007361
10314Q2007243
10334Q2007255
10354Q2007384
10394Q2007397

proc sql;
create table grt as
select
t1.id,
          t1.Qtr,
          t1.val,
    case when val<500 then
    sum(t1.val) as scr  else  val end as scr
    from data t1
    group by t1.id order by qtr;quit;

please help me out

result needed is

idqtrvalscrcnt
10181Q20074934930
10311Q20071751750
10331Q20072272270
10351Q20071881880
10391Q20075455450
10182Q20074419341
10312Q20071012761
10332Q20072955221
10352Q20073125001
10392Q20076076070
10183Q20074588992
10313Q20072815573
10333Q20073576522
10353Q20075775770
10393Q20075165160
10184Q20073618192
10314Q20072435193
10334Q20072555502
10354Q20073846962
10394Q200739710042
Respected Advisor
Posts: 4,173

Re: Problem in retaining observations in sql

I believe this would be quite hard and inefficient to formulate in pure SQL syntax (it's possible that someone will prove me wrong).

I believe the approach could either be a Teradata Stored Procedure (http://www.info.teradata.com/edownload.cfm? itemid=102320056) which you then "kick-off" from within SAS  - or eventually if you are on SAS9.3 data step 2 (PROC DS2) could be an option.

What SAS version are you using? And if it's SAS9.3: Do you know if the libraries needed for PROC DS2 have been installed on the Teradata side?

Contributor
Posts: 30

Re: Problem in retaining observations in sql

I am using EG4.2 I don't have  proc ds2 or atleast give me on sas code

I am trying with sas

data dfg;

set data;

scr=0;qb=0;

if  scr<500 then do ;

vli=val;

put vli;

scr=vli+val;

end;

by id notsorted;

run;

but this is adding the corresponding id val not going back to qtr and picking the val

resulting

id       qtr        val   scr

1018 1Q2007 493 986

1031 1Q2007 175 350

1033 1Q2007 227 454

1035 1Q2007 188 376

1039 1Q2007 545 1090

1018 2Q2007 441 882

1031 2Q2007 301 602

1033 2Q2007 295 590

1035 2Q2007 312 624

1039 2Q2007 607 1214

1018 3Q2007 458 916

1031 3Q2007 281 562

1033 3Q2007 357 714

1035 3Q2007 388 776

1039 3Q2007 516 1032

1018 4Q2007 361 722

1031 4Q2007 243 486

1033 4Q2007 255 510

1035 4Q2007 384 768

1039 4Q2007 397 794

tried sql too al most but not sure

proc sql;

create table grt as

select

    id,dt,vl,

    case when vl<500 then

    sum(vl)   else  vl end as scr

    from data t1

    group by id ;

quit;

10181Q20074931753
10182Q20074411753
10183Q20074581753
10184Q20073611753
10313Q20072811000
10311Q20071751000
10312Q20073011000
10314Q20072431000
10331Q20072271134
10334Q20072551134
10333Q20073571134
10332Q20072951134
10353Q20073881272
10351Q20071881272
10352Q20073121272
10354Q20073841272
10393Q2007516516
10391Q2007545545
10392Q2007607607
10394Q20073972065
Ask a Question
Discussion stats
  • 2 replies
  • 381 views
  • 0 likes
  • 2 in conversation