topic how to create a new record and substract it from the other 2 with same id in SAS Programming

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

vraj1 — Wed, 07 Aug 2019 10:22:30 GMT

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

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

Jagadishkatam — Wed, 07 Aug 2019 10:50:58 GMT

please try the below code

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;

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

Kurt_Bremser — Wed, 07 Aug 2019 10:46:27 GMT

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