topic Re: How to group by in PROC REPORT in New SAS User

How to group by in PROC REPORT

MART1 — Tue, 14 Apr 2020 11:48:47 GMT

Hello

I am trying to group by the output of a PROC REPORT (based on the below example, I'd like to only show 4 rows, rather than repeating the APPs)

data have;
input DT$ APP$ Arrived$;
datalines;
JAN19 APP1 Y
JAN19 APP2 Y
JAN19 APP3 Y
JAN19 APP4 N
FEB19 APP1 N
FEB19 APP2 Y
FEB19 APP3 Y
FEB19 APP4 Y
MAR19 APP1 Y
MAR19 APP2 Y
MAR19 APP3 N
MAR19 APP4 N
;run;

proc report data=have nowd;
	column  APP (DT,  Arrived) ;
	define DT / across  'DT' ORDER=DATA;
;
quit;

I have tried many different combinations but I cannot get the right syntax (my main issue seems to be that the variable "Arrived" is not a numeric; for example if I add

define APP / group  'APP';

it complains I don't have any statistics associated.

Many thanks

Re: How to group by in PROC REPORT

PaigeMiller — Tue, 14 Apr 2020 11:53:48 GMT

You haven't explained the output that you want to get from PROC REPORT. What are the rows? What are the columns? What number should appear in each "cell"?

Do you want counts in each "cell"? PROC FREQ will do this.

Re: How to group by in PROC REPORT

ed_sas_member — Tue, 14 Apr 2020 11:56:22 GMT

Hi @MART1

You can try this:

proc report data=have nowd;
	column  APP (DT,  Arrived) ;
	define DT / across  'DT' ORDER=DATA;
	define APP/ group;
	define Arrived / group;
quit;

Re: How to group by in PROC REPORT

ballardw — Tue, 14 Apr 2020 14:12:05 GMT

No complaint about the DT values not being in date order?

Re: How to group by in PROC REPORT

PaigeMiller — Tue, 14 Apr 2020 14:26:22 GMT

@ballardw wrote:

No complaint about the DT values not being in date order?

Right. As soon as the data set has APR19 the ordering is screwed up. Better would be to convert JAN19, FEB19 etc. to actual SAS date values instead of text strings, and then this ordering issue is easily handled.

Re: How to group by in PROC REPORT

MART1 — Wed, 15 Apr 2020 07:27:09 GMT

I see I had to group by all variables; it's working now thanks ed_sas_member

Re: How to group by in PROC REPORT

MART1 — Wed, 15 Apr 2020 07:33:23 GMT

thanks both; in fact I created a dummy table focussing on the group by function; I did not think about the date format. thanks anyway