Help using Base SAS procedures

Proc Report: Repeating group variable values for each line and for multiple columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Proc Report: Repeating group variable values for each line and for multiple columns

I am using proc report to product the following report:

 

Here is the current output:

 

MarketRegionBranch
Central Ohio MarketBuckeye Central Region02800105 Lane Ave BC
Buckeye Central RegionBuckeye Central Region02800117C Northland BC
Buckeye Central RegionBuckeye Central Region02800141C Worthington BC
Buckeye Central RegionBuckeye Central Region02800142C Grandview BC
Buckeye Central RegionBuckeye Central Region02800143C German Village BC
Buckeye Central RegionBuckeye Central Region02800145C Upper Arlington BC
Buckeye Central RegionBuckeye Central Region02800152C Clintonville BC
Buckeye Central RegionBuckeye Central Region02800168C OSU Financial Center BC
Buckeye Central RegionBuckeye Central Region02800683C State St BC
Buckeye Central RegionBuckeye Central Region02800725C Bexley/Main St BC
Buckeye Central RegionBuckeye Central Region02802917 Graceland BM
Buckeye Central RegionCentral Ohio Market AdminNON-BRANCH
Central Ohio Market AdminEast C-Bus Region02800130C Gahanna BC
East C-Bus RegionEast C-Bus Region02800132C Westerville South BC
East C-Bus RegionEast C-Bus Region02800157C New Albany BC
East C-Bus RegionEast C-Bus Region02800164 Schrock Rd BM
East C-Bus RegionEast C-Bus Region02800208 Newark BC
East C-Bus RegionEast C-Bus Region02800209C Westerville North BC
East C-Bus RegionEast C-Bus Region02800549 Stoneridge BC
East C-Bus RegionEast C-Bus Region02800719C Columbus Square BC
East C-Bus RegionEast C-Bus Region02800721C Linden BC
East C-Bus RegionEast C-Bus Region02801747C Polaris BC
East C-Bus RegionEast C-Bus Region02802924 Sunbury Rd BM
East C-Bus RegionEast C-Bus Region02806390C Lewis Ctr BC
East C-Bus RegionNorthwest C-Bus Region02800068C Dubin-Avery Rd BC
Northwest C-Bus RegionNorthwest C-Bus Region02800093C Delaware BC

 

Here is the code used to produce the above report:

proc report data=reln_depth_branch_rank

style(report)={BORDERCOLOR=black BORDERWIDTH=1}

style(column)={BORDERCOLOR=black}

style(header)={BORDERCOLOR=black background = #DBE5F1 foreground=black}

style(summary)={font_weight=bold font_style=Roman};

options missing = ' ';

columns (&mnth_title market region branch hh_count core_reln_depth core_rank ckg

 sav cd a360 he dir_ind mort ccard ann brkg ins eax ida dcard

obp dir_dep digi mdep alerts spc1 total_reln_depth total_rank spc2 single_svc_cnt five_svc_cnt);

define market / Group left 'Market';

compute market;

if market NE ' ' then hold=market;

if market EQ ' ' then market=hold;

endcomp;

define region / Group left 'Region';

compute region;

if region NE ' ' then hold=region;

if region EQ ' ' then region=hold;

endcomp;

define branch / Group left 'Branch';

define hh_count / sum 'HH Count' format=comma12.0;

define core_reln_depth / mean weight=hh_count 'Core Reln Depth' format=comma12.2;

define core_rank / display 'Rank';

define ckg / mean weight=hh_count 'Checking' format=percent12.1;

define sav / mean weight=hh_count 'Savings' format=percent12.1;

define cd / mean weight=hh_count'CD' format=percent12.1;

define a360 / mean weight=hh_count 'Access 360' format=percent12.1;

define he / mean weight=hh_count 'Home Equity' format=percent12.1;

define dir_ind / mean weight=hh_count 'Direct / Indirect Loan' format=percent12.1;

define mort / mean weight=hh_count 'Mortgage' format=percent12.1;

define ccard / mean weight=hh_count 'Credit Card' format=percent12.1;

define ann / mean weight=hh_count 'Annuity' format=percent12.1;

define brkg / mean weight=hh_count 'Brokerage' format=percent12.1;

define ins / mean weight=hh_count 'Insurance' format=percent12.1;

define eax / mean weight=hh_count 'Early Access' format=percent12.1;

define ida / mean weight=hh_count 'ID Alert' format=percent12.1;

define dcard / mean weight=hh_count 'Debit Card' format=percent12.1;

define obp / mean weight=hh_count 'Online Bill Pay' format=percent12.1;

define dir_dep / mean weight=hh_count 'Direct Deposit' format=percent12.1;

define digi / mean weight=hh_count 'Digital Banking' format=percent12.1;

define mdep / mean weight=hh_count 'Mobile Deposit' format=percent12.1;

define alerts / mean weight=hh_count 'Alerts' format=percent12.1;

define spc1 / display '';

define total_reln_depth / mean weight=hh_count 'Total Reln Depth' format=comma12.2;

define total_rank / display 'Rank';

define spc2 / display '';

define single_svc_cnt / mean weight=hh_count 'Single Service' format=percent12.1;

define five_svc_cnt / mean weight=hh_count 'Five+ Services' format=percent12.1;

compute after;

market='Totals';

region='';

endcomp;

rbreak after / summarize;

run;

 

What I actually need is for the Market value to repeat for each row in that column until a new Market value is encountered.  What it is doing is filling in the Region values in the Market column.  Here is an example I did in Excel to demonstrate what the report should look like.

 

MarketRegionBranch
Central Ohio MarketBuckeye Central Region02800105 Lane Ave BC
Central Ohio MarketBuckeye Central Region02800117C Northland BC
Central Ohio MarketBuckeye Central Region02800141C Worthington BC
Central Ohio MarketBuckeye Central Region02800142C Grandview BC
Central Ohio MarketBuckeye Central Region02800143C German Village BC
Central Ohio MarketBuckeye Central Region02800145C Upper Arlington BC
Central Ohio MarketBuckeye Central Region02800152C Clintonville BC
Central Ohio MarketBuckeye Central Region02800168C OSU Financial Center BC
Central Ohio MarketBuckeye Central Region02800683C State St BC
Central Ohio MarketBuckeye Central Region02800725C Bexley/Main St BC
Central Ohio MarketBuckeye Central Region02802917 Graceland BM
Central Ohio MarketCentral Ohio Market AdminNON-BRANCH
Central Ohio MarketEast C-Bus Region02800130C Gahanna BC
Central Ohio MarketEast C-Bus Region02800132C Westerville South BC
Central Ohio MarketEast C-Bus Region02800157C New Albany BC
Central Ohio MarketEast C-Bus Region02800164 Schrock Rd BM
Central Ohio MarketEast C-Bus Region02800208 Newark BC
Central Ohio MarketEast C-Bus Region02800209C Westerville North BC
Central Ohio MarketEast C-Bus Region02800549 Stoneridge BC
Central Ohio MarketEast C-Bus Region02800719C Columbus Square BC
Central Ohio MarketEast C-Bus Region02800721C Linden BC
Central Ohio MarketEast C-Bus Region02801747C Polaris BC
Central Ohio MarketEast C-Bus Region02802924 Sunbury Rd BM
Central Ohio MarketEast C-Bus Region02806390C Lewis Ctr BC
Central Ohio MarketNorthwest C-Bus Region02800068C Dubin-Avery Rd BC
Central Ohio MarketNorthwest C-Bus Region02800093C Delaware BC

 

Any help would be greatly appreciated.  Thanks!

 

Scott


Accepted Solutions
Solution
‎03-16-2018 01:59 PM
SAS Super FREQ
Posts: 9,365

Re: Proc Report: Repeating group variable values for each line and for multiple columns

[ Edited ]

Hi, Scott:
When you define variables as GROUP variables, the output will ALWAYS look different than the detail data file.

For example, consider this report using SASHELP.CLASS and look at the difference between using DISPLAY, ORDER and GROUP in the number of output rows.

proc report data=sashelp.class;
title '1) DISPLAY usage -- shows 19 rows from data plus summary row';
column sex age;
define sex / display;
define age / mean;
rbreak after / summarize;
run;

proc report data=sashelp.class;
title '2) ORDER usage -- also shows 19 rows from data plus summary row';
column sex age;
define sex / order;
define age / mean;
rbreak after / summarize;
run;

proc report data=sashelp.class;
title '3) GROUP usage shows 2 rows from data plus summary row';
title2 ' one row for each summarized value of the SEX variable';
column sex age;
define sex / group;
define age / mean;
rbreak after / summarize;
run;

If you use GROUP usage for a variable, then you are essentially asking for the rows to be summarized. Only ORDER and DISPLAY usage will give you the same number of rows on the report as you have rows of data.

 

SASHELP.CLASS has 19 observations. When I use PROC REPORT for #1 or #2 reports, I gets 19 rows plus the summary row from the RBREAK statement. When I use #3 with GROUP usage, then I see only 2 rows plus my summary row on the report, because GROUP usage causes the groups to collapse down and produce whatever statistic you ask for in the DEFINE statement for your numeric variables.

 
cynthia

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,365

Re: Proc Report: Repeating group variable values for each line and for multiple columns

Without data, no one can run and test your code. Also, what is &mnth_title -- is it a spanning header or a macro variable that resolves to a variable name. I don't see a DEFINE statement for it, so how you are using it is not clear. Also, you don't show ALL your code? What is your desired output? HTML? RTF? PDF? You are using style overrides, so the assumption might be that you are not using LISTING output. Can you post ALL your code and some test data?
Cynthia
Occasional Contributor
Posts: 6

Re: Proc Report: Repeating group variable values for each line and for multiple columns

Posted in reply to Cynthia_sas

Thanks, Cynthia for the response.  The code for this project is miles long, so I created an Excel file with some fake test data and modified my proc report procedure to use only that data.  Basically, the proc report output should look like the Excel file, but you can see that, for the "Market" column, that is not the case.

 

I attached the test data Excel file and my SAS code that imports that data and then uses proc report for the output.   Any assistance you can provide is very appreciated.

 

Thanks,

Scott

 

Occasional Contributor
Posts: 6

Re: Proc Report: Repeating group variable values for each line and for multiple columns

For some reason, this board won't let me attach the SAS project, so I pasted the proc report portion of the code below.  I simply used the import wizard to import the Excel file I attached in my last post (attached again in this post), then ran the proc report code.

 

Again, thanks for any help you can provide.

 

Scott

 

 

proc report data=test_data

style(report)={BORDERCOLOR=black BORDERWIDTH=1}

style(column)={BORDERCOLOR=black}

style(header)={BORDERCOLOR=black background = #DBE5F1 foreground=black}

style(summary)={font_weight=bold font_style=Roman};

options missing = ' ';

columns (market region branch hh_count );

define market / Group left 'Market';

compute market;

if market NE ' ' then hold=market;

if market EQ ' ' then market=hold;

endcomp;

define region / Group left 'Region';

compute region;

if region NE ' ' then hold=region;

if region EQ ' ' then region=hold;

endcomp;

define branch / Group left 'Branch';

define hh_count / sum 'HH Count' format=comma12.0;

compute after;

market='Totals';

region='';

endcomp;

rbreak after / summarize;

run;

SAS Super FREQ
Posts: 9,365

Re: Proc Report: Repeating group variable values for each line and for multiple columns

[ Edited ]

And again, you did not explain your ODS destination or show all your code. If you are using EG, then your destination is determined by the Tools/Options setting -- what is that? I see you took &mnth_title out of your code. I don't typically open Excel files.

But even so, I don't understand what you want. You don't show the HH_COUNT column in your "desired" output, so I have no idea what you want to do or what you mean when you say that "it is filling in the Region values in the Market column".

 

So, let's bypass the issue of your data and both use SASHELP.PRODSALE, which you should have and you should be able to run my code. When I do the type of thing that I think you want to do, I do not use the code that can be generated by EG. I don't like the code that EG writes for PROC REPORT.

 

 When I need to "fill in" rows, I always use a separate temporary variable for each compute block and I also check the value of the automatic _BREAK_ variable and I also use a length statement for my temporary variables. So, that means I would have one "hold" variable for Market and a second, differently named "hold" variable for Region. In my code, I call them HOLDMKT and HOLDREG. And my logic is slightly different from what you used.

 

I recycled some variables in SASHELP.PRDSALE using this code to make work.test_data (REGION was already in PRDSALE data). Market has 3 possible values CANADA, GERMANY and U.S.A.) and Region has 2 possible values (EAST,WEST) and Branch has 2 possible values (CONSUMER, EDUCATION):

data test_data;
  length market $15 region $15 branch $15;
  set sashelp.prdsale;
  market = country;
  branch = division;
  hh_count = round(actual / 100, 1);
run;

And I got this output:

fill_in_report.png

 

When I run this code:


proc report data=test_data;
title '1) default group behavior suppresses repetitive row values';
columns (market region branch hh_count );
define market / group;
define region / group;
define branch / group;
define hh_count / sum;
rbreak after / summarize;
compute after;
  market='Grand Total';
endcomp;
run;

proc report data=test_data;
title '2) filling in blank rows';
columns (market region branch hh_count );
define market / group;
define region / group;
define branch / group;
define hh_count / sum;
rbreak after / summarize;
compute market;
  length holdmkt $15;
  if market ne ' ' then holdmkt = market;
  else if market = ' ' and _break_ = ' ' then market = holdmkt;
endcomp;
compute region;
  length holdreg $15;
  if region ne ' ' then holdreg = region;
  else if region = ' ' and _break_ = ' ' then region = holdreg;
endcomp;
compute after;
  market='Grand Total';
endcomp;
run;

  I hope this points you in the right direction.

cynthia

Occasional Contributor
Posts: 6

Re: Proc Report: Repeating group variable values for each line and for multiple columns

Posted in reply to Cynthia_sas

Thanks for getting back to me again.  The only thing missing from the code is the code to import the Excel file, which includes the test data.  I used the EG import wizard for that (that's the only way I know how).  I do want HH Count in the result, which is normally an HTML output, similar to proc print.  I took &mnth_title out of the code because it was from a huge macro that I eliminated when I pared this down for your testing purposes.  I know you said you don't open Excel files, so I'm not sure how to get around that.

 

But, if you use the test data in the Excel file by importing it, then run this code, you can compare the output to the imported data set and you will see that they are different.

 

I would be happy to contact you by phone to explain, but I feel like I've already taken too much of your time.

 

Scott

 

Solution
‎03-16-2018 01:59 PM
SAS Super FREQ
Posts: 9,365

Re: Proc Report: Repeating group variable values for each line and for multiple columns

[ Edited ]

Hi, Scott:
When you define variables as GROUP variables, the output will ALWAYS look different than the detail data file.

For example, consider this report using SASHELP.CLASS and look at the difference between using DISPLAY, ORDER and GROUP in the number of output rows.

proc report data=sashelp.class;
title '1) DISPLAY usage -- shows 19 rows from data plus summary row';
column sex age;
define sex / display;
define age / mean;
rbreak after / summarize;
run;

proc report data=sashelp.class;
title '2) ORDER usage -- also shows 19 rows from data plus summary row';
column sex age;
define sex / order;
define age / mean;
rbreak after / summarize;
run;

proc report data=sashelp.class;
title '3) GROUP usage shows 2 rows from data plus summary row';
title2 ' one row for each summarized value of the SEX variable';
column sex age;
define sex / group;
define age / mean;
rbreak after / summarize;
run;

If you use GROUP usage for a variable, then you are essentially asking for the rows to be summarized. Only ORDER and DISPLAY usage will give you the same number of rows on the report as you have rows of data.

 

SASHELP.CLASS has 19 observations. When I use PROC REPORT for #1 or #2 reports, I gets 19 rows plus the summary row from the RBREAK statement. When I use #3 with GROUP usage, then I see only 2 rows plus my summary row on the report, because GROUP usage causes the groups to collapse down and produce whatever statistic you ask for in the DEFINE statement for your numeric variables.

 
cynthia

Occasional Contributor
Posts: 6

Re: Proc Report: Repeating group variable values for each line and for multiple columns

Cynthia,

 

Please ignore my last post.  I missed some of your earlier post.

 

BUT NO MATTER!!!!

 

Because the solution you provided works perfectly!  I can't tell you how awesome that is.  You are a genius!  Thank you so much!

 

Scott

Super User
Posts: 13,498

Re: Proc Report: Repeating group variable values for each line and for multiple columns

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Even using Proc Import there is possible for people to get a different resulting dataset. The guessing component of the procedure uses a setting in the registry for how many rows to examine when importing Excel files with Proc Import. Some folks may have modified that setting (specifically because they do lots of Excel imports) to look at more rows than your current setting. So a variable type or length might differ. And since Proc Import for Excel relies on have the SAS ACCESS\Interface to PC Files not everyone can use proc import.

I am also not confident that different settings in different Excel installs might not affect the results of proc import.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

The data step code generated can be copied and run by anyone with SAS and generate a data set that behaves exactly as your existing data set.

Occasional Contributor
Posts: 6

Re: Proc Report: Repeating group variable values for each line and for multiple columns

Thank you very much!

 

Scott

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 276 views
  • 0 likes
  • 3 in conversation