BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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
Kurt_Bremser
Super User

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 

View solution in original post

2 REPLIES 2
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
Kurt_Bremser
Super User

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 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 354 views
  • 0 likes
  • 3 in conversation