DATA Step, Macro, Functions and more

insert the last observation for every two or three observations in the main data set

Reply
New Contributor
Posts: 2

insert the last observation for every two or three observations in the main data set

Hi, i have a data set like this

 

data test;
input x$ a b c;
cards;
A 1 2 3
B 4 5 6
C 7 8 9
D 1 2 6
E 3 7 9
Total 10 20 30
;
run;

I would like to create a data set like below. Which means i want to repeat the last row of my data set with every 2  records of my existing data set. Please help?

 

A 1 2 3
B 4 5 6
Total 10 20 30
C 7 8 9
D 1 2 6
Total 10 20 30
E 3 7 9
Total 10 20 30

Super User
Posts: 5,441

Re: insert the last observation for every two or three observations in the main data set

Posted in reply to sas715626

By just quick looking at your sample data, it makes little sense. Perhaps you could use more realistic names/values? And tell us why you wish to do this...

Data never sleeps
New Contributor
Posts: 2

Re: insert the last observation for every two or three observations in the main data set

[ Edited ]

Thanks for your response.

I have a real time scenario where if there are 100 rows and there will be a final row which is the sum of 100 rows , so total there will be 101 rows. I have to print 10 rows for each page + the sum row.The 11 th row is the sum of all 100 rows and nothing to do with the 10 rows.  So it will be like 11 rows for 10 pages. Hope it is clear. Please let em know if you need more information w.r.t this

Super User
Posts: 11,343

Re: insert the last observation for every two or three observations in the main data set

Posted in reply to sas715626

This sounds more like something for Proc Report than a data set issue. Data sets do not have pages but proc report has tools to break printed output at changes is the value of a grouping variable, to the sums (and other statistics) with in the group and then provide an overal summary at the end.

SAS Employee
Posts: 24

Re: insert the last observation for every two or three observations in the main data set

Posted in reply to sas715626

This should do the trick for you:

data test;

input x$ a b c;

cards;

A 1 2 3

B 4 5 6

C 7 8 9

D 1 2 6

E 3 7 9

Total 10 20 30

;

run;

data last;

set test end=end;

if end then output;

run;

data final(drop=lastx lasta lastb lastc);

set test end=end;

if _n_=1 then set last(rename=(x=lastx a=lasta b=lastb c=lastc));

if mod(_n_,2)=0 then do;

output;

x=lastx;

a=lasta;

b=lastb;

c=lastc;

end;

if not end then output;

run;

PROC Star
Posts: 1,760

Re: insert the last observation for every two or three observations in the main data set

Posted in reply to michelle_delaurentis_sas_com

Or

data TEST;
input X $ A B C;
cards;
A 1 2 3
B 4 5 6
C 7 8 9
D 1 2 6
E 3 7 9
Total 10 20 30
run;

data FINAL;
  set TEST nobs=NOBS ;
  if _N_ ne NOBS then output;
  if mod(_N_,2)=0 then do;
    set TEST point=NOBS ;
    output;
  end;
run;
Ask a Question
Discussion stats
  • 5 replies
  • 221 views
  • 2 likes
  • 5 in conversation