DATA Step, Macro, Functions and more

Retaining values

Reply
Contributor
Posts: 30

Retaining values

data vnt;

input id 2. dt $6. vl 4.;

datalines;

1 1q2007 200

2 1q2007 200

3 1q2007 200

1 2q2007 20

2 2q2007 120

3 2q2007 620

1 3q2007 150

2 3q2007 250

3 3q2007 700

1 4q2007 50

2 4q2007 100

3 4q2007 300

;

run;

I am trying to get this output as i.e  when ever my sr val is <500 then i have to add my vl of particular qrt id up to reach >=500

id                dt                vl              sr

11q2007200200
21q2007200200
31q2007200200
12q200720220
22q2007120320
32q2007620620
13q2007150370
23q2007250570
33q2007700700
14q200750420
24q2007100670
34q20073001000

tried with this piece

data sdf;

set vnt;

sr=0;

a1=dt;

retain vl

if sr<500 then

sr=sr+vl;

by id notsorted;

put _all_;

run;

please help me

data sdf;

set vnt;

sr=0;

a1=dt;

if sr<500 then do until (sr>=500);

sr=sr+vl;

put sr;

end;

by id notsorted;

put _all_;

run;

here problem is when my value vl is < 500 then i have to go back to 1 qtr and add prev qtr vl corresponds to the id

Respected Advisor
Posts: 3,156

Re: Retaining values

Try this:

data vnt;

input id 2. dt $6. vl 4.;

datalines;

1 1q2007 200

2 1q2007 200

3 1q2007 200

1 2q2007 20

2 2q2007 120

3 2q2007 620

1 3q2007 150

2 3q2007 250

3 3q2007 700

1 4q2007 50

2 4q2007 100

3 4q2007 300

;

proc sort data=vnt;

by id;

run;

data want;

set vnt;

by id;

retain sr;

if first.id then sr=0;

sr=ifn(vl<=500, sr+vl*(vl<=500), vl);

run;

proc print;run;

Regards,

Haikuo

Super Contributor
Posts: 1,636

Re: Retaining values

what is your desired output?

Contributor
Posts: 30

Re: Retaining values

for me the data should not be sorted coz its millions of records as history then i will get fresh quarter file and have to append it i need without sorting the data data will be according to quarter

desired output is

id                dt                vl              sr

11q2007200200
21q2007200200
31q2007200200
12q200720220
22q2007120320
32q2007620620
13q2007150370
23q2007250570
33q2007700700
14q200750420
24q2007100670
34q20073001000
Super User
Posts: 5,504

Re: Retaining values

Under the conditions you have outlined, I doubt you will get a practical solution.  I hope somebody proves me wrong.

You might be better served keeping each quarter separate instead of creating one huge file.  Storing them as SAS data sets, each already sorted by ID, would give you enough flexibility.  Each program could choose which quarters to bring in, and could choose the order:

data want;

   set q1_2007

         q2_2007

         q3_2007;

   by id dt;

run;

OR

data want;

   set q1_2007

         q2_2007

         q3_2007

         ;

   by dt id;

run;

Neither DATA step would require any sorting.

Good luck.

Contributor
Posts: 30

Re: Retaining values

Posted in reply to Astounding

NO it has to be in one table as reference and every quarter have to be appended

Super User
Posts: 5,504

Re: Retaining values

Perhaps the hashing experts should take a look??

Without hashing, this could be done if you have a ton of memory (basically enough memory to hold the entire data set in a couple of two-dimensional arrays).  But if the data set is so large that sorting isn't practical, then I question whether such large arrays would be possible.

Super User
Posts: 10,028

Re: Retaining values

Very interesting. It is dynamically changing value.

data vnt;
input id 2. dt $6. vl 4.;
datalines;
1 1q2007 200
2 1q2007 200
3 1q2007 200
1 2q2007 20
2 2q2007 120
3 2q2007 620
1 3q2007 150
2 3q2007 250
3 3q2007 700
1 4q2007 50
2 4q2007 100
3 4q2007 300
;
run;
data want;
 set vnt;
 array _a{3} _temporary_ (0 0 0);
 if vl lt 500 then do;sr=vl+_a{id};_a{id}=sr;end;
  else do;sr=vl;_a{id}=vl; end;
run;

Ksharp

Contributor
Posts: 30

Re: Retaining values

But there are 22k distinct  ids but there are only 20 values for quarters so it array have to be with dt values which are limited

Super User
Posts: 10,028

Re: Retaining values

That is not a problem.

Array can hold 22k elements, That is easy .

Only if your data looks exactly like what you posed.

data vnt;
input id 2. dt $6. vl 4.;
datalines;
1 1q2007 200
2 1q2007 200
3 1q2007 200
1 2q2007 20
2 2q2007 120
3 2q2007 620
1 3q2007 150
2 3q2007 250
3 3q2007 700
1 4q2007 50
2 4q2007 100
3 4q2007 300
;
run;
proc sql noprint;
select count(distinct id) into : n from vnt;
quit;
%put &n ;
data want;
 set vnt;
 array _a{&n} _temporary_ (&n*0);
 if vl lt 500 then do;sr=vl+_a{id};_a{id}=sr;end;
  else do;sr=vl;_a{id}=vl; end;
run;


Ksharp

Contributor
Posts: 30

Re: Retaining values

data is not exactly like that but my need is like retain the value by when i get recent qtr file when my vl is <500 i have to go back to previous qtr and add previous qtr id vl and count how many qtrs i went back to get sr>=500 ,if i take the qtrs values in array and then proceed?

Super User
Posts: 10,028

Re: Retaining values

Oh. Maybe I understood what you mean.

I will give you some code after twelve hours.

Sorry, I have to leave now, It is too late .

Ksharp

Contributor
Posts: 30

Re: Retaining values

thank you very much for understanding the problem and i tried with the above code but returning the following error

ERROR: Array subscript out of range at line 30 column 24.

when i take off the distinct in sql its running

Super User
Posts: 10,028

Re: Retaining values

Haha.

OK. I have some free time now. Check out whether it is what you want .

data vnt;
input id 2. dt $6. vl 4.;
datalines;
1 1q2007 200
2 1q2007 200
3 1q2007 200
1 2q2007 20
2 2q2007 120
3 2q2007 620
1 3q2007 150
2 3q2007 250
3 3q2007 700
1 4q2007 50
2 4q2007 100
3 4q2007 300
;
run;
data _vnt(drop=_dt);
 set vnt(rename=(dt=_dt));
  dt=input(catx('q',scan(_dt,-1,'q'),scan(_dt,1,'q')),yyq7.);
  format dt yyq7.;
run;

data want(drop=_: ) ;
if _n_ eq 1 then do;
 length id  dt  vl  8; *if 0 then set _vnt;
 declare hash ha(dataset:'_vnt');
  ha.definekey('id','dt');
  ha.definedata('vl');
  ha.definedone();
 end;
 set _vnt;
if vl lt 500 then do;
 _dt=dt;_vl=vl;sr=vl;how_many=0;
 dt=intnx('qtr',dt,-1);
 do while(ha.find() eq 0);
   sr+vl;
   how_many+1;
   dt=intnx('qtr',dt,-1);
   if sr ge 500 then leave;
 end;
 dt=_dt;vl=_vl;
end;
else do;sr=vl; how_many=0;end;
run;

Ksharp

Contributor
Posts: 30

Re: Retaining values

Thank you I will keep working on the data

Ask a Question
Discussion stats
  • 14 replies
  • 440 views
  • 3 likes
  • 5 in conversation