topic Proc report - summarize by freq of variable in SAS Procedures

Proc report - summarize by freq of variable

rkumar23 — Thu, 04 Dec 2014 12:16:52 GMT

I need to calculate no. of observation by group so I used below method however it look like it's messing the MAX value as you see in the output ...Any idea how to circumvent it?

Also just in case if you have more idea to calculate no. of observation by group say DISTNAME in below example via PROC Report itself that would be helpful as well..thanks

DATA LAB1;

INFILE DATALINES truncover ;

INPUT DATE DATE9. GLIBSEQN $ CLUSTER $ DLIB $ DISTNAME $ DEFERR

THRESHOLD ;

DATALINES;

01OCT14 10000 04 10015 XXXXXX15 2 8

01OCT14 10000 04 10015 XXXXXX15 8 8

01OCT14 10000 04 10015 XXXXXX15 7 8

01OCT14 10000 02 10018 yyyyyy18 3 9

01OCT14 10000 02 10018 yyyyyy18 3 10

01OCT14 10000 02 10018 yyyyyy18 8 8

01OCT14 10000 05 10016 ZZZZZZ16 9 8

01OCT14 10000 00 10017 MMMMMM17 10 9

01OCT14 20000 03 1001A KKKKKK1A 12 9

01OCT14 20000 02 10018 yyyyyy18 19 9

01OCT14 20000 05 10016 ZZZZZZ16 20 0

01OCT14 20000 00 10017 MMMMMM17 1 9

;

proc sort data=lab1;by distname;

DATA LAB2;

SET LAB1;

BY DISTNAME;

if first.distname then last = 0 ;

last+1;

if last.distname then output;

RUN;

PROC REPORT DATA=LAB2 NOWD;

COLUMN DATE GLIBSEQN CLUSTER DLIB DISTNAME deferr THRESHOLD last;

DEFINE DATE / GROUP 'DATE' ID ;

define glibseqn / group 'glib' ;

define cluster / group 'cluster';

define dlib / group 'dlib';

define distname / group 'distname';

define deferr / max 'defer value' ;

define threshold / max 'threshold value';

define Last / display 'no. of counts' ;

run;

output produced is

DEFER THRESHOLD NO. OF

DATE GLIB CLUSTER DLIB DISTNAME VALUE VALUE COUNTS

19997 10000 04 10015 XXXXXX15 7 8 3

20000 00 10017 MMMMMM17 1 9 2

02 10018 YYYYYY18 19 9 4

03 1001A KKKKKK1A 12 9 1

05 10016 ZZZZZZ16 20 0 2

Re: Proc report - summarize by freq of variable

RW9 — Thu, 04 Dec 2014 13:02:48 GMT

In your datastep:

DATA LAB2;

SET LAB1;

retain last; /* add this */

Also, please format your code in a readable method (consistent indentations and casing, finishing steps, putting things on separate lines etc.), it is very difficult to read that code.

Re: Proc report - summarize by freq of variable

rkumar23 — Thu, 04 Dec 2014 13:57:17 GMT

RW9, Thanks tried with Retain still output is same...no change the deferr should have max value however it's still showing last observation value...

It look like format of the code changes after it was uploaded on site...may try better next time..

Re: Proc report - summarize by freq of variable

RW9 — Thu, 04 Dec 2014 14:18:45 GMT

Sorry, I thought you were asking about counts? You would be better off sorting your data out first then proc reporting it:

proc sql;

create table DATA_TO_REPORT as

select DATE,

GLIBSEQN,

CLUSTER,

DLIB,

DISTNAME,

DEFERR,

THRESHOLD,

MAX(DEFERR) as MAX_DEFERR,

MAX(THRESHOLD) as MAX_THRESHOLD,

COUNT(1) as NUM_RECORDS

from HAVE

group by GLIBSEQN,

CLUSTER,

DLIB,

DISTNAME;

quit;