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
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
what is your desired output?
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 |
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.
NO it has to be in one table as reference and every quarter have to be appended
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.
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
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
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
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?
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
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
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
Thank you I will keep working on the data
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.