I have a dataset below:
Region | ID | Company | Group | Count | |
East | 12345 | XYZ | Customer | 7 | |
East | 45678 | ABC | Customer | 7 | |
East | 45677 | IUTA | Customer | 7 | |
East | 23456 | JHUY | Provider | 9 | |
East | 09876 | IOPU | Provider | 9 | |
West | 89765 | BNJK | Customer | 12 | |
West | 76545 | OPOU | Customer | 12 | |
West | 32378 | YUIO | Supplier | 10 | |
West | 35357 | IOPI | Provider | 15 | |
West | 72348 | RTYU | Provider | 15 |
I want the output in the following format using proc report.
Region=East
ID Company Group Count
12345 XYZ
45678 ABC
45677 IUTA
--------------------------------------------------------------
CUSTOMER 7
--------------------------------------------------------------
23456 JHUY
09876 IOPU
-------------------------------------------------------------
PROVIDER 9
-------------------------------------------------------------
Region=West
ID Company Group Count
89765 BNJK
76545 OPOU
---------------------------------------------------------------
CUSTOMER 12
---------------------------------------------------------------
I am currently using the below code which is not working out for me. Would appreciate the inputs.
proc report data=test headline headskip nowindows;
by region;
column region ID Company Group Count;
define region/noprint;
define Group/group;
break after count/summarize;
define count/group;
run;
How about :
data have; input Region $ ID Company $ Group $ Count ; cards; East 12345 XYZ Customer 7 East 45678 ABC Customer 7 East 45677 IUTA Customer 7 East 23456 JHUY Provider 9 East 09876 IOPU Provider 9 West 89765 BNJK Customer 12 West 76545 OPOU Customer 12 West 32378 YUIO Supplier 10 West 35357 IOPI Provider 15 West 72348 RTYU Provider 15 ; run; options missing=' '; proc report data=have headline headskip nowindows ; by region; column region ID Company Group Count _Group _Count; define region/noprint; define id/display; define company/display; define Group/display order noprint; define count/analysis mean noprint; define _Group/computed 'Group'; define _count/computed 'count'; compute _Group/character length=20; endcomp; compute after group; _Group=Group; _count=count.mean; endcomp; break after group/summarize; run;
Xia Keshan
How about :
data have; input Region $ ID Company $ Group $ Count ; cards; East 12345 XYZ Customer 7 East 45678 ABC Customer 7 East 45677 IUTA Customer 7 East 23456 JHUY Provider 9 East 09876 IOPU Provider 9 West 89765 BNJK Customer 12 West 76545 OPOU Customer 12 West 32378 YUIO Supplier 10 West 35357 IOPI Provider 15 West 72348 RTYU Provider 15 ; run; options missing=' '; proc report data=have headline headskip nowindows ; by region; column region ID Company Group Count _Group _Count; define region/noprint; define id/display; define company/display; define Group/display order noprint; define count/analysis mean noprint; define _Group/computed 'Group'; define _count/computed 'count'; compute _Group/character length=20; endcomp; compute after group; _Group=Group; _count=count.mean; endcomp; break after group/summarize; run;
Xia Keshan
Thanks a ton Xia! Worked perfectly!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.