BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MART1
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

Capture d’écran 2020-04-14 à 13.56.11.png

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ed_sas_member
Meteorite | Level 14

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;

Capture d’écran 2020-04-14 à 13.56.11.png

MART1
Quartz | Level 8
I see I had to group by all variables; it's working now thanks ed_sas_member
ballardw
Super User

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

PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
MART1
Quartz | Level 8
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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1908 views
  • 0 likes
  • 4 in conversation