BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
nar_sas
SAS Employee
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
SAS Employee
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
SAS Super FREQ
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
Not applicable
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
Super User
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
Fluorite | Level 6
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]

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1253 views
  • 0 likes
  • 5 in conversation