Help using Base SAS procedures

PROC Report – 2 Sets

Reply
N/A
Posts: 0

PROC Report – 2 Sets

Hello everyone,
I have a report with 2 set of Information for the same patient Id.

Patient : A1234 Age: 39
1st Set
Field 1 Field 2 Field 3 Field 4 Field 5
X
Y

2nd Set
Field 6 Field 7 Field 8 Field 9 Field 10
X
Y

I have all the 10 fields in one data set , with the following code I am able to report the first set with no Issue , not sure how to get the 2nd set , followed by the 1st set since the Information belongs to the same Patient Id.
Note : I am writing the output to a .rtf file using ODS.

ods rtf file ="&path\REPORT3.rtf" STYLE=mytable;
proc report data = test_report nowd split=' ' missing headline SPACING = 1
style = [outputwidth = 9 in]
STYLE(HEADER)=[ASIS = ON protectspecialchars = OFF]
STYLE(COLUMN)=[ASIS = ON protectspecialchars = OFF]
STYLE(LINES)=[ASIS = ON protectspecialchars = OFF];

Column FIELD1 FIELD2 FIELD3 FIELD4 FIELD5;
BY pid;

compute before _page_ / Left;
line 'Patient ' STUDYID $20. ' Age:' AGEY;
endcomp;


DEFINE STUDYID / ORDER NOPRINT;
DEFINE AGEY / ORDER NOPRINT;
define FIELD1 /display "FIELD1" style=[just=L cellwidth=25];
define FIELD2 /display "FIELD2" style=[just=C cellwidth=30]; …

ODS RTF CLOSE;
ODS LISTING;
run;

When I try to put 2 report calls , the first set Is printing for all patient Id’s ,

proc report data = test_report nowd split=' ' missing headline SPACING = 1
style = [outputwidth = 9 in]
STYLE(HEADER)=[ASIS = ON protectspecialchars = OFF]
STYLE(COLUMN)=[ASIS = ON protectspecialchars = OFF]
STYLE(LINES)=[ASIS = ON protectspecialchars = OFF];


compute before _page_ / Left;
line '2nd set ';
line ' ';
endcomp;

define FIELD6 /display " FIELD6 " style=[just=L cellwidth=25];
define FIELD7 /display " FIELD7 " style=[just=R cellwidth=22];
RUN;

Can someone please help with the report

Thanks In Advance,
- Swamy
Frequent Contributor
Posts: 75

Re: PROC Report – 2 Sets

Please have a look at your PROC REPORT.
You didnt include the COULMN statement in your PROC. Please include it then you will get the report.

ods rtf file ="&path\REPORT3.rtf" STYLE=mytable;
proc report data = test_report nowd split=' ' missing headline SPACING = 1
style = [outputwidth = 9 in]
STYLE(HEADER)=[ASIS = ON protectspecialchars = OFF]
STYLE(COLUMN)=[ASIS = ON protectspecialchars = OFF]
STYLE(LINES)=[ASIS = ON protectspecialchars = OFF];


compute before _page_ / Left;
line '2nd set ';
line ' ';
endcomp;

define FIELD6 /display " FIELD6 " style=[just=L cellwidth=25];
define FIELD7 /display " FIELD7 " style=[just=R cellwidth=22];
RUN;


Regards

Dhanasekaran R
Allianz Cornhill Information Services
Trivandrum
SAS Super FREQ
Posts: 8,742

Re: PROC Report – 2 Sets

Hi:
These previous forum postings may be useful. They were also on the topic of how to print or wrap one observation's variables and values onto another line.
http://support.sas.com/forums/thread.jspa?messageID=5822ᚾ
http://support.sas.com/forums/thread.jspa?messageID=36259趣
http://support.sas.com/forums/thread.jspa?messageID=34485

In addition, you might want to search on previous forum postings about "dealing with very wide" reports -- which talks about how to alter the report layout to make more of an observation fit on a line. Generally speaking, however, neither PROC PRINT nor PROC REPORT do the kind of splitting or wrapping of an observation such as you want.

cynthia
N/A
Posts: 0

Re: PROC Report – 2 Sets

Thanks Cynthia ,
Very useful Information , while I am trying the methods
My report is little different compare to the reports discussed in the forums, I should have been little more specific , sorry about that.
There is Visit and Vaccination details for an Patient Id , per data there might be more than one visit Information for the same patient Id with different Visit Dates …

Patient : A1234 Age: 39

Visit

Field 1 Field 2 Visit Date Field 4 Field 5
Row 1 .. .. .. ..
Row 2 .. .. .. ..

Vaccination

Field 6 Field 7 Vaccine Type Field 9 Field 10
Row 1 .. .. .. ..
Row 2 .. .. .. ..

Can we still try to achieve this results based on the same methods you have mentioned ?
Please Advice.

Thanks,
- Swamy
SAS Super FREQ
Posts: 8,742

Re: PROC Report – 2 Sets

Hi:
Still having an issue visualizing the input file. For example, what does a representative row look like?? PROC REPORT needs to know what variable will be used as GROUP or ORDER items on the report. What does your sample observation look like??? Is there ONE observation per patient or multiple observations per patient/visit...for example, does your representative observation look like this:
[pre]
patient age field1 field2 visit date field4 field5 field6 field7 field8 vaccine type field9 field10
A1234 39 ?? ?? 1 04/15/10 ?? ?? ?? ?? ?? DPT shot ?? ??
A1234 39 ?? ?? 2 04/16/10 ?? ?? ?? ?? ?? POLIO oral ?? ??
A1234 39 ?? ?? 3 05/03/10 ?? ?? ?? ?? ?? none . ?? ??
[/pre]

cynthia
N/A
Posts: 0

Re: PROC Report – 2 Sets

Cynthia ,
Correct , So based on the example , the report should be BY patient Id , one patient id will have the multiple Visit / Vaccine Information , the output data set have both the Visit / Vaccine information in the same observation.
So report should like the following for the above example :
Patient Id - A1234 Age : 39
Visit
Pid Visit date field3 field4 field5
A1234 04/15/2010
A1234 04/16/2010
A1234 05/03/2010
Vaccine
Vaccine Type Vaccine field7 field9 field10
shot DPT .. ..
oral POLIO
none
Then continue to the same patient id in the data set …
Here is the actual program with the actual variable names , which is working fine for the Visit Information

Please let me know.
**********************************************************************************************
ods noptitle escapechar='~';
options ls=120 ps=40 nodate nonumber orientation=landscape;

ODS PATH RESET;
ODS PATH gen.mytable SASUSER.TEMPLAT SASHELP.TMPLMST;
ods noresults;
ods LISTING close;

ods rtf file ="&path\REPORT1.rtf" STYLE=mytable;

proc report data = outds nowd split=' ' missing headline SPACING = 1
style = [outputwidth = 9 in]
STYLE(HEADER)=[ASIS = ON protectspecialchars = OFF]
STYLE(COLUMN)=[ASIS = ON protectspecialchars = OFF]
STYLE(LINES)=[ASIS = ON protectspecialchars = OFF];

column season pid AGEY SEX setting SPECIALTY CD event_name VIS VISITNUM VDATE DX OTHDX Ranking FILLER1;
BY STUDYID;

compute before _page_ / Left;
line 'Patient ' STUDYID $20. FILLER1 $25. ' Age:' AGEY;
line ' ';
endcomp;

DEFINE PID / ORDER NOPRINT;
DEFINE AGEY / ORDER NOPRINT;
DEFINE FILLER1 / ORDER NOPRINT;
define setting /display "Setting" style=[just=L cellwidth=25];
define SPECIALTY /display "Specialty" style=[just=C cellwidth=30];
define CD /display "Event Number" style=[just=C cellwidth=25];
define event_name /display "Event Name" style=[just=C cellwidth=150];
define vIS /display "Visit" style=[just=C cellwidth=25];
define VISITNUM /display "Visit #" style=[just=C cellwidth=20];
define vDATE /display "Date of Visit" style=[just=C cellwidth=35];
define DX /display "Diagnosis" style=[just=C cellwidth=32];
define OTHDX /display "Other Diagnoses" style=[just=C cellwidth=50];
define Ranking /display "Ranking" style=[just=R cellwidth=22];

RUN;

ODS RTF CLOSE;
ODS LISTING;
run;

**********************************************************************************************
SAS Super FREQ
Posts: 8,742

Re: PROC Report – 2 Sets

I don't understand what is correct??? I showed data with multiple observations per patient ID. You then say that:
"the report should be BY patient Id , one patient id will have the multiple Visit / Vaccine Information , the output data set have both the Visit / Vaccine information in the same observation."

It looks to me that the report should be BY patient ID and age. But then you actually introduce a new variable called STUDYID -- where did STUDYID come from?? What output data set??? PROC REPORT is creating a REPORT in the RTF destination -- NOT an output data set.

Then you show variables in PROC REPORT which do not correspond at all to the "fake" data that I outlined above. I see VISIT and DIAGNOSIS in your REPORT code, but I don't see VACCINE or TYPE??? Where did SPECIALITY and DX and VIS and RANKING and CD and EVENT_NAME (and all the other report items) come from. What happened to those variables??

What does your INPUT data really look like???? I don't understand what you mean when you say:
"Then continue to the same patient id in the data set …" If you use BY group processing, depending on how you write the PROC REPORT code, there is no continuing to the SAME patient ID....I don't understand what you mean by this (probably because I still do NOT have a clear picture of the data that PROC REPORT will be working with.)

cynthia
N/A
Posts: 0

Re: PROC Report – 2 Sets

Cynthia,
Sorry for the confusion, I thought giving some dummy variable names for the discussion perspective,
where else the original program / Data set had different names.

Let me start over with what I have done, and what I am looking for :
Ouput Data set , contain BOTH Visit and Vaccine Information

Pid Sex Setting Specialty VIS VISNUM VADTE CD …….
These fields belong to the Visit Information ( there is some more fields .. )

Same way these are some of the fields with Vaccine Information
Vacnum Vaccine_type Dose Vacccine_date …..

The output is written to a .rft file , which should look like follows , For a patient Id , print all the Visit Information first , then followed by the Vaccine Information , there would more than one visit per patient with different Visit date , Vaccine Type …
*********************************************************************
Patient Id - A1234 Age : 39
Visit
=====
Pid Visit date Vis VisNum
----------------------------------------------------------
A1234 04/16/2010 Y 1
A1234 05/03/2010 N 2
----------------------------------------------------------

Vaccine
=======
Vaccine Type Vaccine
----------------------------------------------------------
shot DPT .. ..
oral POLIO


Patient Id - A1277 Age : 45
Visit
===== ……………

*********************************************************************
Program , which I have created as of now will contain the Visit Information , since I was not sure how to bring the Vaccine Information.

ods noptitle escapechar='~';
options ls=120 ps=40 nodate nonumber orientation=landscape;
ODS PATH RESET;
ODS PATH gen.mytable SASUSER.TEMPLAT SASHELP.TMPLMST;
ods noresults;
ods LISTING close;
ods rtf file ="&path\REPORT1.rtf" STYLE=mytable;
proc report data = outds nowd split=' ' missing headline SPACING = 1
style = [outputwidth = 9 in]
STYLE(HEADER)=[ASIS = ON protectspecialchars = OFF]
STYLE(COLUMN)=[ASIS = ON protectspecialchars = OFF]
STYLE(LINES)=[ASIS = ON protectspecialchars = OFF];
column pid AGEY SEX setting SPECIALTY CD event_name VIS VISITNUM VDATE DX OTHDX Ranking FILLER1;
BY PID;
compute before _page_ / Left;
line 'Patient ' PID $20. FILLER1 $25. ' Age:' AGEY;
line ' ';
endcomp;

DEFINE PID / ORDER NOPRINT;
DEFINE AGEY / ORDER NOPRINT;
DEFINE FILLER1 / ORDER NOPRINT;
define setting /display "Setting" style=[just=L cellwidth=25];
define SPECIALTY /display "Specialty" style=[just=C cellwidth=30];
define CD /display "Event Number" style=[just=C cellwidth=25];
define event_name /display "Event Name" style=[just=C cellwidth=150];
define vIS /display "Visit" style=[just=C cellwidth=25];
define VISITNUM /display "Visit #" style=[just=C cellwidth=20];
define vDATE /display "Date of Visit" style=[just=C cellwidth=35];
define DX /display "Diagnosis" style=[just=C cellwidth=32];
define OTHDX /display "Other Diagnoses" style=[just=C cellwidth=50];
define Ranking /display "Ranking" style=[just=R cellwidth=22];
RUN;
ODS RTF CLOSE;
ODS LISTING;
run;
************************************************************************************
Pls Advice,

- swamy
SAS Super FREQ
Posts: 8,742

Re: PROC Report – 2 Sets

Hi:
Thanks for the information. And for the program. But, the program is not much help really without a clear idea of
1) what the INPUT data structure is and the variable names really are

2) what the output RTF report (RTF file) looks like (this partially comes from your PROC REPORT code and your description of wanting a VISIT section and a VACCINE section) but, we should clearly be able to track EVERY variable value that you show in the input data to something in the RTF file, coming from either the COLUMN statement or the DEFINE statements or the COMPUTE block(s).

3) what the output data set should look like and how you intend to create the output dataset (Note that the ODS RTF statements are NOT creating an OUTPUT dataset. When you use ODS RTF and PROC REPORT, you are routing the output from PROC REPORT to an RTF file. The RTF file is NOT the same as an output dataset.)

4) And, a bonus would be: what is the purpose of your custom style template? If you are using a custom style template, why do you also use options like HEADLINE, and SPACING which are LISTING-only options?? Especially when you explicitly close the LISTING destination at the top of your program. If you have a custom STYLE template, why also have ASIS=ON and PROTECTSPECIALCHAR=OFF in the PROC REPORT syntax instead of in the TEMPLATE code???

So far, you have provided information for #2 but inconsistent information for #1 and #3.

It's your turn to fill in the blanks. What does the information look like in the INPUT file for just 2 patients....showing the REAL variable names that correspond to your COLUMN statement (what happened, BTW, to STUDYID and what is "FILLER1"???). This is what you have in your COLUMN statement, so at least, seeing some fake data (fake IDs, fake AGE, fake specialty, etc) would be VERY helpful:
[pre]
PID AGEY SEX SETTING SPECIALTY CD EVENT_NAME VIS VISITNUM VDATE DX OTHDX RANKING FILLER1
[/pre]

I wonder whether your data really looks like this (or has more variables and/or a different structure). Would a PROC PRINT of the INPUT dataset (called WORK.OUTDS look like this or would there be more variables??):
[pre]

PID AGEY SEX SETTING SPECIALTY CD EVENT_NAME VIS VISITNUM VDATE DX OTHDX RANKING FILLER1
A1234 39 M ?? ? ? ?? ? 1 04/15/10 ?? ?? ?? ??
A1234 39 M ?? ? ? ?? ? 2 04/16/10 ?? ?? ?? ??
A1234 39 M ?? ? ? ?? ? 3 05/03/10 ?? ?? ?? ??
A1277 45 F ?? ? ? ?? ? 1 04/15/10 ?? ?? ?? ??
A1277 45 F ?? ? ? ?? ? 2 04/16/10 ?? ?? ?? ??
[/pre]

Could you fill in the ?? so there's -something- in each column other than ???. Are these ALL the variables?? (More on this below.) Do PID, AGEY and SEX repeat on every observation?? You only show PID in the BY statement. What happens to AGEY and SEX?? I see that you are using AGEY as an ORDER and NOPRINT item, what about SEX -- it is in the COLUMN statement -- should it be an ORDER or a DISPLAY report item?? What about FILLER1?? It is at the end of the COLUMN statement, but you show it with ORDER USAGE and NOPRINT -- this is a little unusual for an ORDER item to be at the end of the report row. What are some representative values for FILLER1??? Is it the same on every row for every PID??

Is your COMPUTE BEFORE _PAGE_ working as you expect??? Remember that PROC REPORT works from Left to Right as the items are placed on each report row?? It is the order of report items in the COLUMN statement that determines when an items will be available for a COMPUTE BEFORE situation (and not the order of the report items in the DEFINE statements -- DEFINE statement order means is not significant.)

For #3, you said:
"...what I am looking for :
Ouput Data set , contain BOTH Visit and Vaccine Information

Pid Sex Setting Specialty VIS VISNUM VADTE CD …….
These fields belong to the Visit Information ( there is some more fields .. )

Same way these are some of the fields with Vaccine Information
Vacnum Vaccine_type Dose Vacccine_date ….. "


If the input dataset is related to the variables in your COLUMN statement, where do these variables in the output dataset come from??? You do not show VACNUM, VACCINE_TYPE DOSE VACCCINE_DATE, in your COLUMN statement. I figure you do not show them because your first PROC REPORT is VISIT info. Is the VISIT data and the VACCINE data in the same dataset or in 2 different datasets??

It is almost impossible to figure out how to show a possible answer to your 2 section question if we never see ALL the variables in the input dataset structure. Are there more variables in your input dataset than those listed in the COLUMN statement?? Do some of the variables in the input dataset need to be transformed or renamed before they make it to the output dataset?? How do you envision creating the output dataset (and remember that the RTF file is NOT the same as an output dataset).

While posting the program code is useful, it is not as useful as seeing, really seeing, a small bit of sample data. People who might have the time or inclination to help you could interpret your statements about the data and the output dataset a number of different ways. If they pick or create some combination of fake data to test your VISIT code and see how it works, it might turn out that they make the wrong decisions about what the fake data looks like and might make assumptions about usage or treatment that are incorrect. And even if they guess right on the VISIT portion of your report, they might guess wrong about the VACCINE portion of your report just because they have to guess about the true structure of the input data.

cynthia
Ask a Question
Discussion stats
  • 8 replies
  • 227 views
  • 0 likes
  • 3 in conversation