BookmarkSubscribeRSS Feed
BobyGadu
Calcite | Level 5

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

14 REPLIES 14
Haikuo
Onyx | Level 15

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

Linlin
Lapis Lazuli | Level 10

what is your desired output?

BobyGadu
Calcite | Level 5

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

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.

BobyGadu
Calcite | Level 5

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

Astounding
PROC Star

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.

Ksharp
Super User

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

BobyGadu
Calcite | Level 5

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

Ksharp
Super User

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

BobyGadu
Calcite | Level 5

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?

Ksharp
Super User

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

BobyGadu
Calcite | Level 5

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

Ksharp
Super User

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

BobyGadu
Calcite | Level 5

Thank you I will keep working on the data

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
  • 14 replies
  • 1565 views
  • 3 likes
  • 5 in conversation