Quartz | Level 8

## how to create a new record and substract it from the other 2 with same id

I have a dataset in the below format

data M.TEST;
infile datalines dsd truncover;
input id:BEST12. score:BEST12. seq:BEST12. vis:BEST12.;
datalines4;
1,22,1,1
1,23,2,1
2,12,1,1
2,22,2,1
3,11,1,1
3,23,2,1
3,19,3,2
3,13,4,2
;;;;

 id score seq vis 1 22 1 1 1 23 2 1 2 12 1 1 2 22 2 1 3 11 1 1 3 23 2 1 3 19 3 2 3 13 4 2

I want to create a new record for every id and subtract scores of  seq 2 to seq 1 and get the scores for the third one and seq regenerating based on new order.Can anyone help me on how to do it.

 id score seq vis 1 22 1 1 1 23 2 1 1 1 3 1 2 12 1 1 2 22 2 1 2 10 3 1 3 11 1 1 3 23 2 1 3 12 3 1 3 19 4 2 3 13 5 2 3 -6 6 2
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: how to create a new record and substract it from the other 2 with same id

See this:

``````data have;
infile datalines dsd dlm=',' truncover;
input id:BEST12. score:BEST12. seq:BEST12. vis:BEST12.;
datalines;
1,22,1,1
1,23,2,1
2,12,1,1
2,22,2,1
3,11,1,1
3,23,2,1
3,19,3,2
3,13,4,2
;

data want;
retain id score seq vis; /* combines retain for new seq and order of columns */
set have (drop=seq);
by id;
_score = lag(score);
if first.id
then seq = 1;
else seq + 1;
output;
if mod(_n_,2) = 0
then do;
score = score - _score;
seq + 1;
output;
end;
drop _score;
run;

proc print data=want noobs;
run;``````

Result:

```id    score    seq    vis

1      22      1      1
1      23      2      1
1       1      3      1
2      12      1      1
2      22      2      1
2      10      3      1
3      11      1      1
3      23      2      1
3      12      3      1
3      19      4      2
3      13      5      2
3      -6      6      2
```
2 REPLIES 2
Amethyst | Level 16

## Re: how to create a new record and substract it from the other 2 with same id

``````data TEST;
infile datalines dsd truncover;
input id:BEST12. score:BEST12. seq:BEST12. vis:BEST12.;
datalines4;
1,22,1,1
1,23,2,1
2,12,1,1
2,22,2,1
3,11,1,1
3,23,2,1
3,19,3,2
3,13,4,2
;;;;

proc sort data=test;
by id vis;
run;

data want;
do until(last.id);
set test;
by id vis;
output;
end;
do until(last.id);
set test;
by id vis;
lags=lag(score);
if last.vis then do;
score=score-lags;
seq=seq+1;
output;
end;
end;
drop lags;
run;

proc sort data=want;
by id vis;
run;

data want2;
set want(drop=seq);
by id vis ;
retain seq;
if first.id then seq=1;
else seq+1;
run;

proc sort data=want2;
by id vis seq;
run;``````
Thanks,
Jag
Super User

## Re: how to create a new record and substract it from the other 2 with same id

See this:

``````data have;
infile datalines dsd dlm=',' truncover;
input id:BEST12. score:BEST12. seq:BEST12. vis:BEST12.;
datalines;
1,22,1,1
1,23,2,1
2,12,1,1
2,22,2,1
3,11,1,1
3,23,2,1
3,19,3,2
3,13,4,2
;

data want;
retain id score seq vis; /* combines retain for new seq and order of columns */
set have (drop=seq);
by id;
_score = lag(score);
if first.id
then seq = 1;
else seq + 1;
output;
if mod(_n_,2) = 0
then do;
score = score - _score;
seq + 1;
output;
end;
drop _score;
run;

proc print data=want noobs;
run;``````

Result:

```id    score    seq    vis

1      22      1      1
1      23      2      1
1       1      3      1
2      12      1      1
2      22      2      1
2      10      3      1
3      11      1      1
3      23      2      1
3      12      3      1
3      19      4      2
3      13      5      2
3      -6      6      2
```
Discussion stats
• 2 replies
• 358 views
• 0 likes
• 3 in conversation