topic Manipulating Proc Report in New SAS User

Manipulating Proc Report

Kirito1 — Fri, 13 Jan 2023 07:12:59 GMT

I was working on a data for which I have to generate a report using sas.

The below code and data screenshot are provided.

PROC SQL;
CREATE TABLE DESIRED_OUTPUT AS
SELECT  T1.*, T2.*, T3.*, T4.*
FROM FINAL_RESULT1 T1
LEFT JOIN FR3 T2 ON (T1.Emp_Code)=(T2.FIN_EMP_CODE)
LEFT JOIN FRA3 T3 ON (T1.Emp_Code)=(T3.FIN_EMP_CODE)
LEFT JOIN FRU3 T4 ON (T1.Emp_Code)=(T4.FIN_EMP_CODE);
QUIT;

Now, Desired Output in proc report what I expected is something like below First EMP_code followed by frequency of 10JAN2023 then 11JAN2023 then 12JAN2023 and followed by total of 1JAN2023 till today frequency total.:

The result I am getting is somewhat absurd.....The code for proc report and result are pasted as screenshot:

PROC REPORT DATA=DESIRED_OUTPUT nowd spanrows
style(report)=[JUST=CENTER OUTPUTWIDTH=95% CELLSPACING=2 BORDERCOLOR=BLACK BORDERWIDTH=2]
STYLE(HEADER)={BORDERCOLOR=BLACK FOREGROUND=WHITE BACKGROUND=MAROON FONT=("zurich BT",11pt)}
STYLE(COLUMN)={TAGATTR="WRAP" JUST=CENTER FONT=("zurich BT",08pt) OUTPUTWIDTH=0.05IN BACKGROUND=LIGHTYELLOW
FOREGROUND=BLACK BORDERCOLOR=BLACK};
TITLE FONT="Zurich BT" bold height=6 "<U>Date wise :</U>" JUSTIFY=LEFT;


col (Emp_Code) (DATE_OF_APPROVAL) (N) (TOTAL_COUNT) (target);


define Emp_Code/DISPLAY "Employee Code" width=20  group order order=data style(column)=[font=("zurich BT",11pt)];
define DATE_OF_APPROVAL/'' ACROSS NOZERO ORDER=INTERNAL "DOA" width=10 style(column)=[font=("zurich BT",11pt)];
define N/ANALYSIS "Count datewise" format=comma11. style(column)=[TAGATTR="WRAP" JUST=RIGHT font=("zurich BT",11pt)];
define TOTAL_COUNT/ANALYSIS "Total from JAN till date" format=comma11. style(column)=[TAGATTR="WRAP" JUST=RIGHT font=("zurich BT",11pt)];
define target/ANALYSIS "TARGET" format=comma11. style(column)=[TAGATTR="WRAP" JUST=RIGHT font=("zurich BT",11pt)];

rbreak after /summarize ol skip;
compute after;
       if _BREAK_ eq '_RBREAK_' then do; 
        RESPYM = 'Total';
        CALL DEFINE(_ROW_ ,"style","style=[BACKGROUND=MAROON FOREGROUND=WHITE]");
    end;
endcomp;
run;

RESULT:

All I want is for 'N' or 'Count datewise' to show under their respective dates.

Any help would be appreciated.

Thank You in advance for all the contributors.

Re: Manipulating Proc Report

ballardw — Fri, 13 Jan 2023 10:28:34 GMT

We cannot code against screen shots. How you build your data set if we do not have the data to start with is next to irrelevant.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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 </> icon or attached as text to show exactly what you have and that we can test code against.

Nesting columns is not really proc reports strong point, i.e. N (or Count whatever) within date for example.

See if this gets anywhere near close.

proc tabulate data=desired_output;
   class emp_code date_of_approval;
   var target;
   table empcode,
         (date_of_approval='DOA' All='Total From Jan')*n=' '
          target*sum=' '
   ;
run;

Caution: If you use variable names like N it becomes doubly important to provide example data as we can't tell when you are using your variable or the Statistic.

Re: Manipulating Proc Report

PaigeMiller — Fri, 13 Jan 2023 11:04:28 GMT

As requested by me earlier, and by @ballardw as well, we need data as working SAS data step code. We cannot write working code from screen captures. Please do not ignore these requests further. We're trying to help you, but you have to help us.

You can nest average and N under the year in PROC REPORT, if your data is set up properly.

Example: if you have a column named YEAR and a column named MEAN and a column named TOTAL_COUNT, then in PROC REPORT you could use

columns emp_code year,(mean total_count);

where YEAR is defined as an across variable.