BookmarkSubscribeRSS Feed
oujiang
Calcite | Level 5
I recently encountered a problem, I would like to find each "qu" average. I want the results of the annex. My code is like this.

proc sql noprint ;

select count(distinct  name) into : sl from i;

quit;

proc report data=lk_5 nowd SPANROWS  out=rep(drop=n _break_) noCOMPLETECOLS

  style(report)=[cellspacing=2 borderwidth=2 bordercolor=blue font_face="gb_2312"]

  style(header)=[foreground=GREEN font_face="gb_2312"]

  style(column)=[foreground=black brown   font_face="times new roman" font_size=2 ]

  style(lines)=[foreground=black background=black   font_style=italic font_weight=bold font_size=2 font_face="gb_2312"]

  style(summary)=[ background=cxaeadd9   font_face="times new roman" font_size=2 just=r] split='|';

  column  a b  ('Standard scores' score=mean_biao) qu, name,defen  ('average' defen=average) n ('ratio' scoreratio);

  define qu /across '' center;

  define name/across '' center ;

  define a /group '';

  define b /group '';

  define score/ group  center;

  define mean_biao / analysis mean ''  format=12.  center;

  define defen / analysis  '' format=12.2 center '';

  define average / analysis  mean'' format=12.2 center '' ;

  define n /center '' noprint;

  define scoreratio /  '' format=percent7.2 center ;

    compute scoreratio;

        scoreratio=average/mean_biao;

        endcomp;

break after a/ summarize suppress;

        compute after a;

                                b='subtotal';

                                mean_biao=mean_biao*n/&sl;

                                average=average*n/&sl;

        endcomp;

  rbreak after /summarize;

                compute after;

                        a='total';

                        mean_biao=mean_biao*n/&sl;

                        average=average*n/&sl;

        endcomp;

quit;

1 REPLY 1
Cynthia_sas
SAS Super FREQ

Hi:

  I am not sure what you mean by "results of the annex". And when you say you want the average 'qu' variables, do you mean you want the average nested underneath each QU and NAME combination next to the DEFEN value or do you want an extra summary line after each GROUP of A or B.

  You sent you workbook, but it's not clear to me whether that workbook is an example of what you WANT to see or an example of something else????

  You did not show any ODS statements in your code, so it is hard to figure out what you want to do.

  There have been many previous forum postings (with code examples) on the use of ACROSS variables and ABSOLUTE column names for computations in a COMPUTE block. My suggestion is that you do a bit of searching on the forum for some of the previous postings. To get you started, I've posted a simple program that shows how to divide the SALES variable value by the RETURNS variable value (using SASHELP.SHOES)t together using a single ACROSS variable. Using the OUT= option with PROC REPORT will give you a chance to preview how ABSOLUTE column names work.

cynthia

proc sort data=sashelp.shoes out=shoes;
  by region subsidiary product;
  where product in ('Slipper', 'Sandal') and
        region in ('Asia', 'Pacific');
run;

          

options nocenter nodate nonumber;

ods listing close;

          

ods html file='c:\temp\show_across.html' style=sasweb;

proc report data=shoes nowd out=lookatme;
   title '1) This Report Shows missing for PCTRET item';
   column region subsidiary product,(sales returns pctret);
   define region / group f=$10.;
   define subsidiary / group f=$10.;
   define product/across;
   define sales / analysis f=comma8.;
   define returns/ analysis f=comma8.;
   define pctret / computed;
   compute pctret;
      pctret = .;
   endcomp;
   break after region / summarize;
run;

                  

proc print data=lookatme;
  title '1a) This is how PROC REPORT internally names the columns';
run;

                  

proc report data=shoes nowd;
   title '2) This Report Uses ABSOLUTE column names/numbers';
   column region subsidiary product,(sales returns pctret);
   define region / group f=$10.;
   define subsidiary / group f=$10.;
   define product/across;
   define sales / analysis f=comma8.;
   define returns/ analysis f=comma8.;
   define pctret / computed f=percent8.2;
   compute pctret;
      _c5_ = _c4_ / _c3_;
      _c8_ = _c7_ / _c6_;
   endcomp;
   break after region / summarize;
   compute after region;
      line ' ';
   endcomp;
run;

ods _all_ close;

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
  • 1 reply
  • 686 views
  • 0 likes
  • 2 in conversation