BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
Committe Status Date Consortium ingrant protocol
DMCC rcvd 01/01/2010 abc 123 1 590
NIH apprvd 02/01/2010 abc123 2 590
o/DSMB apprvd 02/01/2010 abc123 3 590
DMCC apprvd 02/10/2010 bdv123 1 680
NIH rcvd 01/10/2010 bdv123 2 680
o/DSMB apprvd 02/10/2010 bdv123 3 680



how to get ouput following

consortia ingrant protocol dmcc nih
rcvd apprvd rcvd apprvd
7 REPLIES 7
Cynthia_sas
SAS Super FREQ
Hi:
I don't understand. The top part of your original post is data????
[pre]
Committe Status Date Consortium ingrant protocol
DMCC rcvd 01/01/2010 abc123 1 590
NIH apprvd 02/01/2010 abc123 2 590
o/DSMB apprvd 02/01/2010 abc123 3 590
DMCC apprvd 02/10/2010 bdv123 1 680
NIH rcvd 01/10/2010 bdv123 2 680
o/DSMB apprvd 02/10/2010 bdv123 3 680
[/pre]

and the output you want is:
[pre]
consortia ingrant protocol dmcc nih rcvd apprvd rcvd apprvd
[/pre]

OR the output you want is:
[pre]
dmcc nih
consortia ingrant protocol rcvd apprvd rcvd apprvd
[/pre]

Here are some questions:
1) What procedures have you tried?
2) The variables shown in your desired output do not match the variables you showed in the data that you posted. Do you need some kind of transformation (such as a transpose to happen before you report)? Somehow, it looks like you want to turn the variable values of DMCC and NIH into column headers??? Or possibly use PROC REPORT or PROC TABULATE for getting the report??? What about o/DSMB -- what happens to those observations???
3) What is your destination output of choice?
4) Do you need a report?
5) If you do not need a report, do you need an output dataset?

Can you explain a bit about the input data and how it corresponds or is related to the output you posted????

cynthia
SASPhile
Quartz | Level 8
The top part is data:

and bottom part is the expected out.Due to space limitations i did not include o/dsmb variable.

For the following code,there is a blank box apppearing on the top left corner.how to suppress the box.

the code used is:
options pagesize=45 linesize=114 orientation=landscape ;
ods listing close; ods escapechar='~';
ods rtf style =rtf body = "GEN.I.06.Protocol Review and Approval.rtf" notoc_data;
proc report data = rp_basic (where=(con_funding ne ' ')) missing nowindows split = '*' headskip out = test
style(column)=[font_size=1.75 ]
style(report) = {outputwidth =9.75in frame = _undef_};
column(con_funding consortia2 con_anticprotnum protocol)status_Date2, committee, date dummy ;
define consortia2/'Consortia' group order = data style=[cellwidth=1.0 in ]
flow ;
define Con_anticprotnum/'# in Grant' group center flow style=[cellwidth=0.5 in ] ;
define protocol/ 'Protocol' group width=20 flow ;
define committee/across ' ' order = data format= $CO. ;
define date/across ' ' order = data;
define con_funding/group order=data noprint flow ;
define status_date2/ ' ' ;
define dummy/'d' noprint;
compute before con_funding /style = {font_weight = bold background =lightblue};
line con_funding $CF. ;
endcomp;
break after con_funding/skip;
compute after consortia2/ style(lines) = {font_size= 0.1pt protectspecialchars=off background = black}; line ' ' ;
endcomp;
/*compute before consortia2/ style(lines) = {font_size= 0.1pt protectspecialchars=off background = black}; line ' ' ;
endcomp;*/
title "RDCRN";
title2 "GEN.I.06";
title3 "Protocol Development" ;
title4 "Protocol Review by Institute and Consortium" ;
title5 "Data Current as of &rptdate";
run;
ods rtf close;
ods listing;
Cynthia_sas
SAS Super FREQ
Hi:
Is the data you originally posted the same as the data used for this report??? In the data from your original post, I thought the variable names were:
[pre]
Committe Status Date Consortium ingrant protocol
DMCC rcvd 01/01/2010 abc123 1 590
NIH apprvd 02/01/2010 abc123 2 590
o/DSMB apprvd 02/01/2010 abc123 3 590
DMCC apprvd 02/10/2010 bdv123 1 680
NIH rcvd 01/10/2010 bdv123 2 680
o/DSMB apprvd 02/10/2010 bdv123 3 680
[/pre]

and in the COLUMN statement in this post, you have:
[pre]
column(con_funding consortia2 con_anticprotnum protocol)status_Date2, committee, date dummy ;
[/pre]

Where do CON_FUNDING CONSORTIA2 CON_ANTICPROTNUM STATUS_DATE2 COMMITTEE and DUMMY come from???? I only see DATE and PROTOCOL in the above data that appear in the COLUMN statement.

Also, if you are interested in RTF, you are using some options, like HEADSKIP, FLOW, that are only used in the LISTING destination. In addition, your PAGESIZE and LINESIZE options are not used by RTF, at all. Only the ORIENTATION=LANDSCAPE is used by ODS RTF.

But those are minor issues. I don't actually see how the originally posted data can be used in this PROC REPORT step....there are so many variables missing from the posted data.

cynthia
Cynthia_sas
SAS Super FREQ
Sorry, I found COMMITTEE. Did you mean for STATUS_DATE2 to be an ACROSS item??? It looks like you want:
[pre]
STATUS_DATE2 crossed with COMMITTEE crossed with DATE
[/pre]
which implies that STATUS_DATE2 would ALSO be an ACROSS item. because in your COLUMN statement you have:
[pre]
... status_Date2, committee, date ...
[/pre]

The comma operator in PROC REPORT means an ACROSS variable. But, in your PROC REPORT code, you have this for STATUS_DATE2 (no usage):
[pre]
define committee/across ' ' order = data format= $CO. ;
define date/across ' ' order = data;
define status_date2/ ' ' ;
[/pre]

cynthia
Cynthia_sas
SAS Super FREQ
Hi:
I made some fake data using sashelp.prdsale and I'm not sure what you mean by an "empty" box in the top left.

If you have this COLUMN Statement:
[pre]
column (con_funding consortia2 con_anticprotnum protocol)
status_Date2, committee, date dummy ;
[/pre]

then you will have 3 header rows on the report. Let's say, for example, that STATUS_DATE2 had 2 possible values of 11 or 22; that COMMITTEE had 3 possible values of o/DSMB, DMCC or NIH and that DATE could have 2 possible values of 01/01/1994 or 02/01/1994. So, the report headers will lay out like this:
[pre]
--------------------------------------------------------------------------***** TOP OF REPORT TABLE *****---------------------------------------------------------------------------
11 22
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
o/DSMB DMCC NIH o/DSMB DMCC NIH
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# in
Consortia Grant Protocol 01/01/1994 02/01/1994 01/01/1994 02/01/1994 01/01/1994 02/01/1994 01/01/1994 02/01/1994 01/01/1994 02/01/1994 01/01/1994 02/01/1994
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[/pre]

The rows of dashes show where, in my RTF output file, I see border lines between each header row in the table. Yes, there are empty cells above "Consortia # in Grant and Protocol....are these the cells you mean?? They -are- empty above these 3 variable headers because PROC REPORT reserves an entire report row for the values of COMMITTEE and a separate report row for the values of STATUS_DATE2. This is how ACROSS items work...every variable value becomes a column going across the report. On the row for COMMITTEE and on the row for STATUS_DATE2, there is NOTHING to put above the headers Consortia, # in Grant, and Protocol.

Below is the program that I used to fake some data and the HTML and RTF outputs that I created. When I wrote the code, I took out all the LISTING only options like WIDTH, FLOW and HEADSKIP, PAGESIZE and LINESIZE. I also simplified the syntax a bit and added some white space to make the code easier to read. I generally find that outputwidth=100% works better for me, especially with ACROSS -- when I used your original value of 9.75, the table did not fit on the landscape page. I think that's because, especially with ACROSS items, PROC REPORT has some pretty complicated calculating to do to take into account the size of the the values needed for the nested column headers.

Looking at the report, I wonder if an alternate meaning is the fact that "# in Grant" is taking up 2 rows... which puts an empty space above Consortia and Protocol. I believe that's happening because there's not enough room in your cellwidth for the whole string "# in Grant" so that header wraps to 2 lines.

cynthia

[pre]
data rp_basic;
length committee $6 consortia2 $6 con_funding $1;
set sashelp.prdsale;
** make con_funding variable;
con_funding = country;

** make committee variable;
if region = 'EAST' then committee = 'NIH';
else if region = 'WEST' then committee = 'DMCC';
if region = 'EAST' and country = 'CANADA' then committee = 'o/DSMB';

** make consortia2;
if division = 'CONSUMER' then consortia2 = 'abc123';
else consortia2 = 'bdv123';

** make protocol;
if division = 'CONSUMER' then protocol = 590;
else protocol = 680;

if region = 'EAST' then status_date2 = quarter+10;
else if region = 'WEST' then status_date2 = quarter + 21;

date = month;

con_anticprotnum = year;
dummy = 1;
if year = 1994 and month(date) in (1,2) then output;
format date mmddyy10.;
run;

%let rptdate = May 21, 2010;
options orientation=landscape ;
ods listing close;
ods escapechar='~';

ods html body="GEN_I_06_Protocol.html" style=sasweb;
ods rtf body = "GEN_I_06_Protocol.rtf" notoc_data ;
proc report data = rp_basic (where=(con_funding ne ' '))
missing nowindows split = '*'
style(report)={outputwidth=100%}
style(column)={font_size=8pt}
style(header)={font_size=9pt};
column (con_funding consortia2 con_anticprotnum protocol)
status_Date2, committee, date dummy ;
define con_funding/group order=data noprint;
define consortia2/'Consortia'
group order = data style(column)=[cellwidth=1.0in];
define Con_anticprotnum/'# in Grant' group
center flow style(column)=[cellwidth=0.5in ] ;
define protocol/ 'Protocol' group ;
define status_date2/ across' ' ;
define committee/across ' ' order = data ;
define date/across ' ' order = data;
define dummy/'d' noprint;

compute before con_funding /
style = {just=left font_weight = bold background =lightblue};
line con_funding $25.;
endcomp;
break after con_funding/skip;
compute after consortia2/
style(lines) = {font_size= 0.1pt protectspecialchars=off background = black};
line ' ' ;
endcomp;

title "RDCRN";
title2 "GEN.I.06";
title3 "Protocol Development" ;
title4 "Protocol Review by Institute and Consortium" ;
title5 "Data Current as of &rptdate";
run;
ods _all_ close;
ods listing;
title;

[/pre]
SASPhile
Quartz | Level 8
Hi Cynthia,
I'm wondering if we can supress the white space or box crreated on top of Consortia #inGrant Protocol variables so that o/DSMB DMCC NIH will be in the same row.
SasPhile.
Cynthia_sas
SAS Super FREQ
Hi,
well this is where I am confused again...do you want DATE to be an ACROSS item or not ???
The only way to put the COMMITTEE values on the same row with those other headers would be if DATE was NOT an ACROSS item. That's why I asked about your COLUMN statement and the number of across variables.

Cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1226 views
  • 0 likes
  • 2 in conversation