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 |
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
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.