SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How can I recode value

Reply
N/A
Posts: 0

How can I recode value

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
SAS Employee
Posts: 38

Re: How can I recode value

Posted in reply to deleted_user
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?
SAS Employee
Posts: 38

Re: How can I recode value

Posted in reply to deleted_user
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;
SAS Super FREQ
Posts: 8,868

Re: How can I recode value

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
N/A
Posts: 0

Re: How can I recode value

Posted in reply to 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
Super User
Posts: 10,044

Re: How can I recode value

Posted in reply to deleted_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
Occasional Contributor
Posts: 7

Re: How can I recode value

Posted in reply to deleted_user
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]
Ask a Question
Discussion stats
  • 6 replies
  • 272 views
  • 0 likes
  • 5 in conversation