BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
The dataset main has the following:

terrid phy_name total
90110 John smith 200
90110 Ellis Newman 100
90110 Ricardo Noto 300




The dataset pdrp has these physicians:
phys_name
Ricardo Noto


when physician in main is also present in pdrp then do not display his total
at physician level, but at it should be included in the grand total as shown:


terrid phy_name total
90110 John smith 200
90110 Ellis Newman 100
90110 Ricardo Noto .
90110(Grand total) 600


How to display this?
8 REPLIES 8
Cynthia_sas
SAS Super FREQ
Hi:
PROC REPORT will not "join" two data sets together. However, if you had these 2 files and did your own join, you could make a variable, shown in my code as the SHOWTOT variable which PROC REPORT could use to decide whether to show the total or not.

So assuming the existence of your two datasets, the code below shows a merge and then 2 PROC REPORT steps. The first PROC REPORT steps shows all the logic and reveals ALL the variables. The second PROC REPORT step shows the use of NOPRINT to hide the variables that were used to control what was displayed in the REPTOT computed report item. The original TOTAL variable is still used (but NOPRINT) so that the TOTAL.SUM can be assigned to computed REPTOT at the end of the report, when the break happens.

Also, you did not say what would happen if you had physicians in PDRP data but not found in the MAIN data -- don't know whether this is a scenario that could be encountered. I took care of it with a WHERE in the PROC REPORT.

cynthia
[pre]
** code assumes MAIN and PDRP are both sorted and that the;
** variable is PHYNAME in both files;

** could also use PROC SQL for this join, but will use the SHOWTOT variable in PROC REPORT;
** did not say how you want to handle an obs in PDRP data but not in MAIN data;
data allinfo;
merge main(in=inmain) pdrp(in=inpdrp);
by phyname;
if inmain and not inpdrp then showtot = 'y';
if inpdrp and not inmain then showtot = 'x';
if inpdrp and inmain then showtot = 'n';
run;

proc print data=allinfo;
run;

ods listing;
ods html file='c:\temp\showtot.html' style=sasweb;
proc report data=allinfo nowd;
title '1) Show all report items';
column terrid phyname showtot total reptot;
where showtot ne 'x';
define terrid / display;
define phyname / display;
define showtot / display;
define total / sum;
define reptot / computed;
rbreak after / summarize;
compute reptot;
if showtot = 'n' then reptot = .;
else if showtot = 'y' then reptot = total.sum;
if _break_ = '_RBREAK_' then do;
phyname = 'Grand Total';
reptot = total.sum;
end;
endcomp;
run;

options missing = ' ';
proc report data=allinfo nowd;
title '2) Use NOPRINT to suppress display of "working" items';
footnote 'Note: blank total means physician was in PDRP data file';
column terrid phyname showtot total reptot;
where showtot ne 'x';
define terrid / display;
define phyname / display;
define showtot / display noprint;
define total / sum noprint;
define reptot / computed 'Total';
rbreak after / summarize;
compute reptot;
if showtot = 'n' then reptot = .;
else if showtot = 'y' then reptot = total.sum;
if _break_ = '_RBREAK_' then do;
phyname = 'Grand Total';
reptot = total.sum;
end;
endcomp;
run;
ods _all_ close;

title; footnote;

[/pre]
SASPhile
Quartz | Level 8
Cynthia,
Thanks for the help. I tried to use your logic in my code but it did not run as expected.I'm including my code that incorporated your logic.
the total_scripts is same as "total" on your code.

ods html file='T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\Calldata\Outputfiles\nomissing.xls';

proc report data=allinfo nowd;
title '1) Show all report items';
column terrid bsm physician_name showtot total_scripts reptot,year,productgroup;
where showtot ne 'x';
define terrid/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define bsm/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE physician_name / group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE year / across ''
STYLE (header) = {just=left font_size=1};
DEFINE productgroup / across
STYLE (header) = [FOREGROUND = black font_size=1];
define showtot / display;
define total_scripts / sum;
define reptot / computed;
rbreak after / summarize;
compute reptot;
if showtot = 'n' then reptot = .;
else if showtot = 'y' then reptot = total_scripts.sum;
if _break_ = '_RBREAK_' then do;
physician_name = 'Grand Total';
reptot = total_scripts.sum;
end;
endcomp;
break after terrid/summarize skip ol style=[font_weight=bold background=grey];
compute after terrid ;
TerrID =left(trim(TerrID))||' Total';
endcomp;
run;
ods html close;


/***************************/
final report should be of this format:

terrid bsm total 2007(comp) 2007(mydrug)
9011101(Total) SSAR 4,357,917 9,246,000 3,417,629 12,866,200 785,600 5,975,600
SASPhile
Quartz | Level 8
The datalayout (I'm not able to show all the fields due to space restriction) is:
terrid bsm phy_name total
90110 SSAR John smith 200
90110 SSAR Ellis Newman 100
90110 SSAR Ricardo Noto 300

The dataset pdp has these physicians:
phys_name
Ricardo Noto

when physician in main is present in pdrp then do not display his total
at physician level, but at it should be included in the grand total as shown:


terrid bsm phy_name total
90110 SSAR John smith 200
90110 SSAR Ellis Newman 100
90110 SSAR Ricardo Noto .
90110(total) SSAR 600


My report contains these fields:
terrid bsm physician_name total_scripts ,year,productgroup
grouped at:terrid bsm physician_name
across variables:year,productgroup
sum is:total_scripts

I tried to use the showtot and reptot variables from your logic.But couldnt succeed.
Cynthia_sas
SAS Super FREQ
Hi:
Ah, you did not show an ACROSS situation in your original posting, which is why my example did not work for you.

...when you use ACROSS items, the naming convention changes for how you refer to items in the COMPUTE block. PROC REPORT assigns -internal- absolute column names, such as _c1_, _c2_, _c3_ to every across report item on a report row, and when you have items nested, such as you now show, you cannot refer to your variable as just REPTOT. You will have REPTOT under YEAR1, PRODUCTGROUP1, and REPTOT under YEAR1, PRODUCTGROUP2 and REPTOT under YEAR2, PRODUCTGROUP1, etc, etc.

If you look in the PROC REPORT documentation and examine the output from the program below, you should be able to get an idea of how this works and how your COMPUTE block would have to change.

Especially useful is the fact that you can use OUT= to reveal the absolute names that PROC REPORT assigns internally. So just as my code is showing the XXX variable as _C5_, _C8_ and _c11_, once you get your nestings arranged the way you want, you may find that the absolute column name for REPTOT might be _c4_, _c6_, _c8_ or _C10_ (depending on how many year/product combos you have).

You will also probably find that you need total_scripts to be nested under each year and product group, too. It doesn't make sense that your total_scripts would be outside the nesting. But you'll have to decide that based on what your real data looks like.

It is hard, but not impossible to figure the absolute column names. You need several pieces of information -- how many report items appear BEFORE the across variable(s) and how many unique combos of across item(s) there are. Then you should notice from my examples, that the naming falls into a pattern. (And if you search the doc, you will find some algorithms for how to figure what the absolute column names will be.) But the above is just a guess based on what your data might actually look like and whether I was right about every year/productgroup combination having a separate total_script value.

At any rate, if you look at the structure of SASHELP.PRDSALE and then at these 2 examples, they should give you an idea of how absolute column names work. SASHELP.PRDSALE has only 2 years and 2 values for PRODTYPE. So my total possible ACROSS unique combinations are 4 possible values:
[pre]
--------1993-------|--------1994-------|
FURNITURE | OFFICE |FURNITURE | OFFICE |
[/pre]

Each year has 2 possible values for PRODTYPE. So NOW, the absolute column names will be set on how many items are nested under each unique YEAR/PRODTYPE combinations and how many report items come BEFORE the first ACROSS variable. The program contains 2 separate examples and I haven't used NOPRINT to hide anything. You can see that the absolute column names in the first example are different than the absolute column names in the second example. This is because in the first example, I have 3 columns nested under each unique combination of YEAR/PRODTYPE and in the second example, I only have 2 columns nested under each unique combination of YEAR/PRODTYPE.

The 2nd example might be closer to what you're trying. I suggest you really take the time to read up on how PROC REPORT processes and uses ACROSS variables to understand what you want to code. You will not be able to just cut and paste my code into your PROC REPORT -- you'll have to adjust it based on your -real- data and the number of unique combinations you have going ACROSS in your data.

cynthia

[pre]
ods html file='c:\temp\show_across.html' style=sasweb;
proc report data=sashelp.prdsale nowd out=whatcols;
title '1a) with ACROSS';
column country region year,prodtype,(actual predict xxx) ;
define country / group;
define region / group;
define year / across 'Based on Year and ProdType Value';
define prodtype / across ' ';
define actual / sum;
define predict / sum;
define xxx / computed;
rbreak after / summarize;
compute xxx;
_c5_ = _c3_ - _c4_;
_c8_ = _c6_ - _c7_;
_c11_ = _c9_ - _c10_;
_c14_ = _c12_ - _c13_;
endcomp;
run;

proc print data=whatcols;
title '1b) What do Absolute Columns Look Like?';
run;

proc report data=sashelp.prdsale nowd out=whatcols2;
title '2a) with conditional logic';
column country region year,prodtype,(actual reptot) ;
define country / group;
define region / group;
define year / across 'Based on Year and ProdType Value';
define prodtype / across ' ';
define actual / sum f=dollar12.;
define reptot / computed f=dollar12.;
rbreak after / summarize;
compute reptot;
if region = 'EAST' and country = 'CANADA' then do;
_c4_ = .;
_c6_ = .;
_c8_ = .;
_c10_ = .;
end;
else do; /* all other conditions */
_c4_ = _c3_;
_c6_ = _c5_;
_c8_ = _c7_;
_c10_ = _c9_;
end;
if _break_ = '_RBREAK_' then do;
country = 'Total';
_c4_ = _c3_;
_c6_ = _c5_;
_c8_ = _c7_;
_c10_ = _c9_;
end;
endcomp;
run;

proc print data=whatcols2;
title '2b) Different Absolute Columns Look Like?';
run;

ods _all_ close;
title; footnote;

[/pre]
SASPhile
Quartz | Level 8
Cynthia,
I tried the following and most part worked.
and I'm wondering if there is ayother way other than manually entering the column names like _c4_ etc etc.Because in the date changes everymonth and each time we run the report, we have to change columns manually?

here is the code:
ods html file="C:\Documents and Settings\skap\Desktop\reptop1.xls";
proc report list NOWINDOWS data=allinfo out=allinfo1 HEADLINE MISSING;
COLUMN terrid bsm physician_name showtot year,productgroup,(reptot total_scripts);
where terrid ne '';
define terrid/group ;
define bsm/group ;
DEFINE physician_name / group ;
DEFINE year / across '' ;
DEFINE productgroup / across;
DEFINE total_scripts / sum format=comma12. ' ';
DEFINE showtot/group noprint;
define reptot/computed ;
compute reptot;
if showtot='n' then do;
_C6_=.;
_C8_=.;
_C10_=.;
_C12_=.;
_C14_=.;
_C16_=.;

end;
else do;
_C7_=_C6_;
_C9_=_C8_;
_C11_=_C10_;
_c13_=_c12_;
_c15_=_c14_;

end;
endcomp;

/****
reason to create compute blocks:
repeat GROUP or ORDER variables for every row.
***/
compute terrid;
if terrid ne '' then hold=terrid;
if terrid eq '' then terrid=hold;
endcomp;
compute bsm;
if bsm ne '' then hold1=bsm;
if bsm eq '' then bsm=hold1;
endcomp;
break after terrid/summarize skip ol style=[font_weight=bold background=grey];
compute
after terrid ; TerrID =left(trim(TerrID))||' Total';
endcomp;
run;
ods html close;
Cynthia_sas
SAS Super FREQ
Hi:
Since the pattern for numbering the ACROSS columns is predictable, you could develop a macro program to take the place of the hard-coded assignment statements. When the macro program resolved into code each month, the right number of absolute column names would be generated.

Such a macro program is outlined as one of the examples in this paper:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

cynthia
SASPhile
Quartz | Level 8
Cynthia ,
I have a question.
I included few more variables in the column statement which will be grouped in the define statement.
But the output seem to be different than expected.The first repot that worked is summary report.The other report is at phsician address and zip level.
Instead of giving the total_scripts and reptot alternatively in the outputdataset, i see only absolute column names and one total_scripts column.
If this works, I will be adding patientid in report.
here is my code:
proc report data=rpt2a_bsm out=rpt_second nowd NOWINDOWS HEADLINE MISSING;
COLUMN terrid region bsm md_zip flag physician_name PHYS_ADDRESS1 PHYSICIAN_ADDRESS2 phys_city md_state year,productgroup(total_scripts reptot);
define terrid/group ;
define region/group ;
define bsm/group ;
define md_zip/group '5 digit zip'
STYLE (header) = {just=left font_size=1 font_face=verdana}
STYLE (COLUMN) = {htmlstyle="mso-number-format:\@" just=left font_size=1.5 font_face=verdana};
DEFINE physician_name / group;
DEFINE year / across;
DEFINE productgroup / across '2007,2008&2009 Total';
DEFINE total_scripts /sum format=comma12. '' ;
DEFINE flag/group ;
define reptot/computed format=comma12. ' ';
define PHYS_ADDRESS1/group;
define PHYSICIAN_ADDRESS2/group;
define phys_city/group 'md_city';
define md_state/group;
compute terrid;
if terrid ne '' then hold=terrid;
if terrid eq '' then terrid=hold;
endcomp;
compute bsm;
if bsm ne '' then hold1=bsm;
if bsm eq '' then bsm=hold1;
endcomp;
compute region;
if region ne '' then hold3=region;
if region eq '' then region=hold3;
endcomp;
/* break after terrid/summarize skip ol style=[font_face=verdana font_weight=bold background=grey];*/
run;
Cynthia_sas
SAS Super FREQ
Hi:
This really needs to be debugged by someone who can look at your -real- data and your -entire- program. I do not see a compute block for REPTOT, even though you list REPTOT as computed in the DEFINE statement.

If you want (TOTAL_SCRIPTS REPTOT) nested underneath YEAR and PRODUCTGROUP, you are missing a comma in your COLUMN statement. Without the missing comma after PRODUCTGROUP-- there is nothing to tell Proc REPORT to put TOTAL_SCRIPTS and REPTOT -UNDER- the YEAR/PRODUCTGROUP nesting. Without a compute block for REPTOT, you would only see the TOTAL_SCRIPTS column.

You still need a compute block with absolute columns in order to get numbers for REPTOT. And remember that if you add more columns to the COLUMN statement before the ACROSS, you will need to change the absolute column numbers. I count 10 variables -before-
year,productgroup,(total_script reptot) <---note comma after PRODUCTGROUP
so that means your numbering will start at _c11_ -- so your compute block for REPTOT will have to be adjusted accordingly (or you'll have to adopt a macro approach).

Now that I see the address fields back in the COLUMN statement, it looks like this posting is related to some of your previous postings. Again, I have trouble understanding whether you actually want a group report or a detail report. Also it seems that sometimes you have indicated some prior processing going on -- such as joins or summarizing -- those do impact the structure of the data that you're sending to PROC REPORT and whether your items need to be ORDER or GROUP.

Without seeing some of the actual input data (instead of just the column names), it's hard to understand how this data is different from any of the data in the previous postings...or why the previous examples of PROC REPORT don't work for this data. Everything in these previous postings about REPORT vs TABULATE still applies:

http://support.sas.com/forums/thread.jspa?messageID=20472俸
http://support.sas.com/forums/thread.jspa?messageID=20574偞
http://support.sas.com/forums/thread.jspa?messageID=23216媰
http://support.sas.com/forums/thread.jspa?messageID=20499倓
http://support.sas.com/forums/thread.jspa?messageID=20354侂

You might wish to work with Tech Support on this one, as the forum postings don't seem to be helping.

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
  • 8 replies
  • 921 views
  • 0 likes
  • 2 in conversation