topic Re: Please provide the answer using first.id and last.id concept in SAS Programming

Please provide the answer using first.id and last.id concept

ROHINISDAS — Mon, 14 Nov 2016 14:10:05 GMT

Question: Dataset schoolx is given as :

data schoolx;
input Name $ Class $ Marks;
cards;
AAA X 20
AAA Y 30
AAA Z 40
AAA X 50
BBB X 60
BBB Y 70
BBB X 20
CCC X 10
CCC Y 40
CCC Z 50
CCC Z 30
;
run;

Generate the following output from the above dataset:
AAA X 70
AAA Y 30
AAA Z 40
BBB X 80
BBB Y 70
CCC X 10
CCC Y 40
CCC Z 80

Re: Please provide the answer using first.id and last.id concept

Kurt_Bremser — Mon, 14 Nov 2016 14:17:11 GMT

First do a proc sort by name and class.

Then, in a data step, use by name class; again.

use

if first.class then newmarks = 0;

to initialize at every group change, use

newmarks + marks;

to add the marks up (this syntax for summarization automatically makes newmarks a retained variable).

use

if last.class then output;

to write out the summarized observations

use

drop marks;

rename newmarks=marks;

to create the old structure.

Re: Please provide the answer using first.id and last.id concept

SuryaKiran — Mon, 14 Nov 2016 14:24:27 GMT

Is there any specific reason for using first.id and last.id. You can use proc means instead.

proc sort data=schoolx;

by name class;

run;

PROC MEANS data=schoolx sum;

class name class;

var marks;

run;

Re: Please provide the answer using first.id and last.id concept

slchen — Mon, 14 Nov 2016 14:24:47 GMT

proc sql;
select Name,Class,sum(Marks) as Marks from schoolx group by Name,Class;
quit;

Re: Please provide the answer using first.id and last.id concept

PeterClemmensen — Mon, 14 Nov 2016 14:39:30 GMT

data schoolx;
input Name $ Class $ Marks;
cards;
AAA X 20
AAA Y 30
AAA Z 40
AAA X 50
BBB X 60
BBB Y 70
BBB X 20
CCC X 10
CCC Y 40
CCC Z 50
CCC Z 30
;
run;

proc sort data = schoolx;
   by Name Class;
run;

data want;
   set schoolx;
   by Name Class;

   if first.class = 1 then do;
      newmarks = 0;
      newmarks = newmarks + marks;
   end;

   else newmarks = newmarks + marks;
   
   if last.class then output;
   retain newmarks;
run;

Re: Please provide the answer using first.id and last.id concept

ROHINISDAS — Mon, 14 Nov 2016 16:11:51 GMT

can u please help me to explain with the output what exactly happens with this portion of code.I meant how first.class and last.class is working here.?

if first.class then newmarks = 0;

to initialize at every group change, use

newmarks + marks;

to add the marks up (this syntax for summarization automatically makes newmarks a retained variable).

use

if last.class then output;

to write out the summarized observations

use

drop marks;

rename newmarks=marks;

to create the old structure.

Re: Please provide the answer using first.id and last.id concept

Kurt_Bremser — Mon, 14 Nov 2016 16:46:24 GMT

Read the very good SAS documentation on by group processing, and you will find the answer yourself.

Re: Please provide the answer using first.id and last.id concept

ROHINISDAS — Mon, 14 Nov 2016 19:13:13 GMT

thanks