topic Re: Get distinct group wise summary in SAS like pivot table in SAS Programming

Get distinct group wise summary in SAS like pivot table

karthik18 — Wed, 24 Mar 2021 17:53:18 GMT

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!

Re: Get distinct group wise summary in SAS like pivot table

PaigeMiller — Wed, 24 Mar 2021 18:30:28 GMT

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;

Re: Get distinct group wise summary in SAS like pivot table

PhilC — Wed, 24 Mar 2021 18:22:21 GMT

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.

Re: Get distinct group wise summary in SAS like pivot table

PaigeMiller — Wed, 24 Mar 2021 18:29:27 GMT

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.