How can I recode value

[deleted_user]

I have a dataset with two variables GROUP and CNT. How can I recode the GROUP to NEWGROUP like this :
if CNT>20, NEWGROUP=GROUP
otherwise, store the GROUP (e.g. in GROUP 2) then sum the value of the CNT to next row until the summation result >20 (14+12), NEWGROUP=stored GROUP (i.e. 2).

data test ;
input GROUP CNT ;
datalines ;
0 32
1 29
2 14
3 12
4 13
5 6
6 4
7 48
8 18
9 24
10 34
;

The output data :

GROUP CNT NEWGROUP
0 32 0
1 29 1
2 14 2
3 12 2
4 13 4
5 6 4
6 4 4
7 48 7
8 18 8
9 24 8
10 34 10

Thanks and regards,

FC

[nar_sas]

Sorry, have to admit a little confusion from my reading.
It’s easy enough to keep a running total of CNT from row to row by retaining the variable and setting back to zero when necessary. I’m just a bit confused by the logic.
For example, with row 3 in the data, CNT on that row is less than 20, so what do you want in NEWGROUP?
Then we get to row 4, so I assume we want sum the value of CNT for that row and the previous row (14 + 12) which is now > 20, so what do you want for NEWGROUP? The value of group from the previous row? Do we reset the counting of row so that with the next row we start over?

[nar_sas]

Based on our current understandingof the problem, this code should work, give it a try.

data test;
input GROUP CNT ;
retain cnt2 0;
retain newgroup 0;
retain prevgroup 0;
retain accumflag 0;
keep group cnt newgroup;

cnt2 = cnt2 + cnt;
if (cnt > 20 or cnt2 > 20) then do;
if (accumflag = 0) then
newgroup = group;
else do;
newgroup = prevgroup;
prevgroup = 0;
accumflag = 0;
end;
cnt2 = 0;
end;

else if (cnt2 <= 20) then do;
/* accumflag = zero, we are starting over and want to save the current group */
if accumflag = 0 then prevgroup = group;
accumflag = 1;
newgroup = prevgroup;
end;

datalines ;
0 32
1 29
2 14
3 12
4 13
5 6
6 4
7 48
8 18
9 24
10 34
;
run;

[Cynthia_sas]

Hi:
It looks like your code got truncated because of the < and/or > symbols in the code. When you make postings of code, these 2 symbols in particular seem to confusing the posting software because they are interpreted as belonging to HTML tags and not to syntax.

When you cut and paste code into the forum, you may need to make a quick pass through and find/replace all < symbols with &lt; and all > symbols with &gt; prior to posting.

Other useful tips for posting code, such as preserving indention levels and blank lines, etc, are contained in this previous forum post:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

cynthia

[deleted_user]

Thanks for your reply. I tried it out.

The program result I get :

GROUP CNT newgroup
0 32 0
1 29 1
2 14 1
3 12 3
4 13 3
5 6 3
6 4 6
7 48 7
8 18 7
9 24 9
10 34 10
11 5 10
12 4 10
13 3 10
14 12 14
15 2 14

Kindly advise how to fix the GROUP 6 in row 7.

Thanks.

FC

[Ksharp]

Hi.
It looks like a little question reduce a big problem.
Be honest, It spend a little much time to think about it, I underestimate its complex degree.


[pre]

data test ;
input GROUP CNT ;
datalines ;
0 32
1 29
2 14
3 12
4 13
5 6
6 4
7 48
8 18
9 24
10 34
;
run;
data temp;
set test;
sum+cnt; output;
if sum gt 20 then sum=0;
run;
data result;
set temp;
retain newgroup;
if _n_ eq 1 then newgroup=group;
if lag(sum) gt 20 then newgroup=group;
run;
[/pre]




Ksharp

[LaurentdeWalick]

This solution should do it with a single pass through the data.

[pre]
data newgroup (drop=runningtotal);
set test;
retain newgroup runningtotal;
/* first observation, so initialize running total and newgroup */
if _N_ = 1 then do;
newgroup = group;
runningtotal = cnt;
end;
else do;
/* if running total gt 20 then start a new group and reset running total */
if runningtotal gt 20 then do;
newgroup = group;
runningtotal = cnt;
end;
/* if running total le 20 retain current group and add current count to running total */
else runningtotal = sum(runningtotal, cnt);
end;
run;
[/pre]