Searching in SAS Anti-Money Laundering: Form Searches and Advanced Searches ... View more

Sharing Jobs Using SAS Visual Analytics ... View more

Hi all - I have a specific report format that I am trying to achieve using PROC REPORT, and I'm getting pretty close, but there's a column subtotal element that I cannot figure out.   I have sales data, summarized by Client, Quarter, and Week.  Each quarter contains an arbitrary number of weeks, up to around 13.   I would like to list Client information down the side, with: Quarters listed across the top, each Quarter's corresponding Weeks nested underneath, the table populated with Sales for each Client-Week, and Grand Totals on the far right and bottom.  I've gotten this far on my own just fine. Where I'm getting stuck is that I'd also like to show Quarterly subtotal columns, at the end of each Quarter.  I have tried different variations of "break after" and the like, but I'm just not getting there.   Does anyone in the community have any suggestions?  Below is some sample code that represents the progress I've made so far.  I've also attached an image showing what I currently "have" versus what I "want."  I greatly appreciate any and all help - cheers!     data one; input client_rank client $9. client_id quarter $ weekending sales; datalines; 1 Apple 12345 Q1 20240106 1000 1 Apple 12345 Q1 20240113 2000 1 Apple 12345 Q1 20240127 5000 1 Apple 12345 Q2 20240413 3000 1 Apple 12345 Q2 20240420 4000 1 Apple 12345 Q2 20240427 2000 2 Microsoft 67890 Q1 20240106 3000 2 Microsoft 67890 Q1 20240113 1000 2 Microsoft 67890 Q1 20240127 2500 2 Microsoft 67890 Q2 20240413 4000 2 Microsoft 67890 Q2 20240420 500 2 Microsoft 67890 Q2 20240427 1500 ; run; proc report data = one nowd; column client_rank client client_id quarter, weekending, sales ("Total" sales=tot); define client_rank / noprint group; define client / "Client" group; define client_id / "Client ID" group; define quarter / " " across; define weekending / " " across nozero; define sales / "Sales" analysis sum format=comma12.0; define tot / "Sales" analysis sum format=comma12.0; rbreak after / summarize; compute after; client = "Total"; endcomp; define client_rank / order group; run;   ... View more

I'm having difficulty writing a piece of SAS code and I'm seeking the most effective approach within SAS. I have a variable named 'type' (with values 1, 2, 3, 4) and client IDs. Essentially, I aim to create another variable called 'modified_type,' which follows these rules: If there's a single unique client ID with only 1 in the 'type' variable, then 'modified_type' should be set to 0. However, if there are multiple client IDs with the same number having 1 in the 'type' variable, 'modified_type' should be set to 1. If there are 2 in the 'type' variable, then 'modified_type' should be set to 2... and so on. I tried using the retain function so something like this: retain modified_type 0; if first.client_id then modified_type = 0; if type = '1' or type = '1st' then modified_type = 1; else if type = '2' or = '2nd' then modified_type = 2; and so on..... but these does seem to retain 0 in the type and for some reason shows as 1.   Yea im note sure the best way to do this, a newbie at SAS so would even appreciate the point in the right direction:   ... View more