Get distinct group wise summary in SAS like pivot table

[karthik18]

Hi Sir,

I have a data set : HAVE

and i want to get output WANT mentioned in the attachment  :

I have already tried below option but not working for me.

PROC SORT DATA= HAVE;

BY NAME;

RUN;

DATA WANT;

SET  HAVE;

BY NAME;

IF FIRST.NAME THEN SUM_NO=0

ELSE SUM_NO+1;

RUN;

Kindly help me to find the result.

Thanks!

[PaigeMiller]

Many of us will not open attachments. If it shows the desired output, put a screen capture in your reply.

From your description, I don't see where "pivot table" applies here. Also, PROC SUMMARY gets these groupwise sums pretty quickly and easily.

proc summary data=have nway;
    class name;
    var numeric_variable;
    output out=want n=sum_no;
run;

[PhilC]

PROC SORT DATA= HAVE;
  BY NAME;
DATA WANT;
  SET  HAVE;
    BY NAME;
  retain SUM_NO;
  IF FIRST.NAME 
    THEN SUM_NO=0
    ELSE SUM_NO+1;
RUN;

You'll have better results if you use a RETAIN statement.  Previously, in your given code, SUM_NO was set to MISSING every time the data step loaded another record.

[PaigeMiller · Accepted solution]

You get the same results without RETAIN, which is not needed for the sum statements.

Example:

PROC SORT DATA= sashelp.class out=have;
  BY sex;
DATA WANT;
  SET  HAVE;
    BY sex;
  IF FIRST.sex 
    THEN SUM_NO=0;
    ELSE SUM_NO+1;
RUN;

I think the original post from @karthik18 is missing a semi-colon, and that may be why it doesn't work. So, @karthik18 please examine your code carefully for a place where you need a semi-colon.

Of course, this still doesn't produce a sum or a count, it produces a "count" where the first record in each group is always 0.