turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- How can I recode value

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-18-2011 11:02 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

02-18-2011 11:24 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

02-18-2011 12:02 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nar_sas

02-20-2011 12:45 PM

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 < and all > symbols with > 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

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 < and all > symbols with > 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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

02-20-2011 08:01 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

02-20-2011 11:58 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

02-23-2011 04:38 AM

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]

[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]