topic Re: Retaining values in SAS Programming

Retaining values

BobyGadu — Tue, 08 May 2012 17:43:10 GMT

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

1	1q2007	200	200
2	1q2007	200	200
3	1q2007	200	200
1	2q2007	20	220
2	2q2007	120	320
3	2q2007	620	620
1	3q2007	150	370
2	3q2007	250	570
3	3q2007	700	700
1	4q2007	50	420
2	4q2007	100	670
3	4q2007	300	1000

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

Re: Retaining values

Haikuo — Tue, 08 May 2012 18:04:37 GMT

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

Re: Retaining values

Linlin — Tue, 08 May 2012 19:50:29 GMT

what is your desired output?

Re: Retaining values

BobyGadu — Wed, 09 May 2012 02:50:15 GMT

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

1	1q2007	200	200
2	1q2007	200	200
3	1q2007	200	200
1	2q2007	20	220
2	2q2007	120	320
3	2q2007	620	620
1	3q2007	150	370
2	3q2007	250	570
3	3q2007	700	700
1	4q2007	50	420
2	4q2007	100	670
3	4q2007	300	1000

Re: Retaining values

Ksharp — Wed, 09 May 2012 08:38:10 GMT

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

Re: Retaining values

BobyGadu — Wed, 09 May 2012 13:15:08 GMT

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

Re: Retaining values

Astounding — Wed, 09 May 2012 14:05:19 GMT

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;

data want;

set q1_2007

q2_2007

q3_2007

;

by dt id;

run;

Neither DATA step would require any sorting.

Good luck.

Re: Retaining values

BobyGadu — Wed, 09 May 2012 14:32:34 GMT

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

Re: Retaining values

Astounding — Wed, 09 May 2012 15:12:19 GMT

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.

Re: Retaining values

Ksharp — Thu, 10 May 2012 08:00:34 GMT

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

Re: Retaining values

BobyGadu — Thu, 10 May 2012 11:17:36 GMT

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?

Re: Retaining values

Ksharp — Thu, 10 May 2012 12:41:41 GMT

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

Re: Retaining values

BobyGadu — Thu, 10 May 2012 13:24:21 GMT

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

Re: Retaining values

Ksharp — Thu, 10 May 2012 14:36:32 GMT

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

Re: Retaining values

BobyGadu — Thu, 10 May 2012 17:30:28 GMT

Thank you I will keep working on the data