About SASaholic629

SASaholic629 · ‎12-27-2022

Thanks for your reply, Cynthia. I might not have been clear about what I want to happen. I don't think it's an issue of whether or not SAS has the font available to use. I'm able to make the individual table created by PROC REPORT to be in Calibri. And when I use ODS Excel to send it to Excel, the table remains in Calibri. My problem is that the surrounding cells, the one SAS is not creating any data for, switches to Arial. I don't want the surrounding cells to change at all. I want them to remain in Calibri (which I know is available since it creates my table in Calibri). Just as they would if I were to do a PROC EXPORT, the entire sheet would remain in Calibri. However for some reason, ODS Excel changes the entire sheet to Arial, and then changes the tables created by the PROC REPORT to whatever font I tell it, in this case, Calibri. I want the entire sheet to remain in Calibri. Not just the cells created by PROC REPORT. I know SAS can allow the entire sheet to remain in Calibri through PROC EXPORT. I want it to behave the same way using ODS Excel.

SASaholic629 · ‎12-22-2022

When I open Excel, the default font is Calibri, font size 11. However, when I use ODS to create a report, it makes the entire sheet Arial, size 10. I don't want it to do that. I want to keep the entire sheet as Calibri, size 11. I am able to make the table Calibri, size 11, but everything outside of that is still Arial, size 10. It looks silly. Why is SAS doing this? Is there a way to prevent it from defaulting to Arial, size 10 for the entire sheet? I just want to leave it as my default Excel settings. Side note: PROC EXPORT doesn't mess with the Excel defaults. It remains as Calibri, size 11. But then my table looks boring, so that doesn't really help me either. My point with this is that SAS leaves the Excel default settings alone for PROC EXPORT but not through ODS. Why is that?

SASaholic629 · ‎08-26-2021

I have 3 variables - Quarter (char), Bucket (char), Value (numeric) I want to us Proc Report to display Quarter going across, and Bucket going down When I do that using the code below, it is not grouping the Bucket going down, but rather duplicating Buckets, so that each value for each quarter is on its own row. DATA Have; INFILE DATALINES MISSOVER DELIMITER='|'; INPUT Qtr :$4. Bucket :$1. Value 8.; DATALINES; 20Q4|A|10 20Q4|B|15 20Q4|C|20 21Q1|A|11 21Q1|B|14 21Q1|C|22 21Q2|A|12 21Q2|B|15 21Q2|C|23 ;RUN; ODS EXCEL FILE="FILEPATH/OutputFile.xlsx"; PROC REPORT DATA=Have; TITLE 'Quarterly'; COLUMNS Bucket Qtr,(Value); DEFINE Bucket / 'Bucket' DISPLAY STYLE=[CELLWIDTH=1.1in FONT_WEIGHT=BOLD]; DEFINE Qtr / ' ' ACROSS ' '; DEFINE Value / 'Value' DISPLAY STYLE=[CELLWIDTH=1.1in]; RUN; ODS EXCEL CLOSE; The result looks like this: 20Q4 21Q1 21Q2 Bucket Value Value Value A 10 . . B 15 . . C 20 . . A . 11 . B . 14 . C . 22 . A . . 12 B . . 15 C . . 23 But I want the result to look like this (including creating a total row for each quarter): 20Q4 21Q1 21Q2 Bucket Value Value Value A 10 11 12 B 15 14 15 C 20 22 23 Total 45 47 50 Is this possible to do, so that when new quarters are added to the data, it will automatically add a new column for the new quarter? And, of course, group them all on the same row for the same bucket.

SASaholic629 · ‎04-15-2019

That all makes sense. Thanks. The correlated variables came from different software, so I have no knowledge of how they were correlated. The idea was to compare downstream results of uncorrelated vs correlated. But the caveat is that we want to use the same seed, to avoid any added noise from using a different seed. It sounds like we'll need to take a different approach then. Thanks again

SASaholic629 · ‎04-15-2019

Rick, I've tested this, and it definitely works to uncorrelate my variables. However, it does not get me back to the starting point. In the example from your blog, the first xy that is created using and independent N(0,1) is very different than the resulting xy when you uncorrelate zw using inverse Cholesky Decomposition. While they are both uncorrelated, the results are very different. Essentially that feels no different than if I were to just pick a different seed for the random generator? Right?

SASaholic629 · ‎04-09-2019

I am starting with correlated data. I was just trying to do something quick. Wasn't sure it would matter what we used in the example to start with. Appreciate your info on inverse Cholesky decomposition. I'll give that a try and report back. Thanks again!

SASaholic629 · ‎04-09-2019

Do you mind showing me using this simple example? How would I transform these 3 variables using PCA? DATA Have; CALL STREAMINIT(123); DO Scenario = 1 to 100; Var1 = RAND("Normal", 0); Var2 = RAND("Normal", 0); Var3 = RAND("Normal", 0); OUTPUT; END; RUN;

SASaholic629 · ‎04-09-2019

I know how to create correlated random draws using PROC IML. However that performs the random drawing and the correlating all at the same time. I already have my random draws (from another program). I'd like to take these existing random draws and correlate them. To be more specific, I want to "un-correlate" them. They need to have zero correlation and be completely independent. BACKGROUND: I have 20 sets of correlated random variables (n=20,000). I use these to simulate 20,000 scenarios and find various VaR, CTE, etc. type statistics. I want to compare those statistics to the same (seed) set of random variables that are completely independent of each other. The idea is to determine the benefit of using correlated random variables to independent ones. Again, it has to be using the same seed, which is why I need to use my existing random draws rather than generating brand new ones. I don't want any noise from using a different seed.

SASaholic629 · ‎09-25-2018

That is exactly what I meant by one step, haha. Didn't want to have to create 20 different proc reports for each tab or whatever. Wasn't familiar with proc tabulate's ability to do that. So thank you, this is a HUGE help. Couple of follow up questions: 1. How do I get the sheet names to be the name of the data in Sheet_Grp instead of 'Tabulate 1 - Table 1' etc. 2. How do I format the numbers? I tried adding FORMAT value COMMA9.; at the end and that didn't do anything.

SASaholic629 · ‎09-25-2018

Is it possible to output to different tabs in Excel based on the value in the data? I have 3 different groupings that I'd like to breakout going down, across, and by sheet name. Can I take the data below to create 2 different tabs called, 'S1' and S2'? Then have 'A1' and 'A2' going across the top of each tab with 'D1' and 'D2' going down each tab? (I would like to do all of this in one step as I will have about 20 different tabs) DATA Have; INPUT Sheet_Grp $ Across_Grp $ Down_Grp $ Value; DATALINES; S1 A1 D1 20 S1 A1 D2 25 S1 A2 D1 30 S1 A2 D2 35 S2 A1 D1 40 S2 A1 D2 45 S2 A2 D1 50 S2 A2 D2 55 ;RUN;

SASaholic629 · ‎08-21-2018

This works really well. Thank you! Only minor change is you have to set running_product to 1 instead of 0. Otherwise it'll always be 0

SASaholic629 · ‎08-20-2018

When asking questions on here, I find it best to keep it as simple as possible. I thought simply writing out the formula would be enough in this case, without going into too much detail. But maybe a longer explanation might help? - I have about 500 sets of 20,000 random scenarios (unratioed losses) - I need to calculate ratioed losses on those unratioed losses by using liabilities from a separate table - That liability table has approximately 100 observations - Before asking my question, I had already brought the two tables together (the 'Value' field is from the Liability table) - Now I need to do the equation where I essentially combine those 500 sets of random losses down to 100 sets of random losses that correspond to my liabilities (this calculates the deltas between loss sets) - There are more pieces involved after this step to get to the ratioed losses, but getting the deltas seems to be the trickiest part Not sure if that helps or not. I appreciate you taking the time to try understand my situation better.

SASaholic629 · ‎08-20-2018

I saw that workaround before posting, but it doesn't handle 0's or missing values very well. I wasn't sure if maybe SAS had addressed this since that workaround was posted. I must say I'm somewhat surprised that SAS doesn't have a PRODUCT function. If it can add N numbers together with a SUM function, why can't it multiply N numbers together with a PRODUCT function??

SASaholic629 · ‎08-20-2018

Same thing as a SUM function, but using multiplication as the operator instead of addition

SASaholic629 · ‎08-20-2018

I have a dataset with 2 different groups. Below is a sample and a PROC SQL for what calculation I need to preform: DATA Have; INPUT Grp1 $ Grp2 $ Scenario Value; DATALINES; A X 1 1.3 A X 2 0.8 A X 3 1 A X 4 0.9 A Y 1 0.7 A Y 2 1.2 A Y 3 0.8 A Y 4 0.9 A Z 1 0.7 A Z 2 0.8 A Z 3 1.1 A Z 4 0.7 B X 1 1 B X 2 0.7 B X 3 1.2 B X 4 1.3 B Y 1 1 B Y 2 0.9 B Y 3 0.7 B Y 4 1.1 ;RUN; PROC SQL; CREATE TABLE Want AS SELECT Grp1, Scenario, (PRODUCT(Value) - 1 ) - ( SUM(Value) - COUNT(Grp2) ) AS Result FROM Have GROUP BY Grp1, Scenario; QUIT; I need to perform the 'Result' calculation in the 'Want' table. However, there doesn't appear to be a PRODUCT function? Am I missing something really obvious here?

Online Status	Offline
Date Last Visited	‎12-27-2022 05:32 PM

Re: ODS Excel - Formatting font for an entire sheet

ODS Excel - Formatting font for an entire sheet

ODS Excel - Proc Report using Across option

Re: Correlate Existing Variables

Re: Correlate Existing Variables

Re: Correlate Existing Variables

Re: Correlate Existing Variables

Correlate Existing Variables

Re: ODS Excel - Tab output based on data

ODS Excel - Tab output based on data

Re: ODS Excel - Proc Report using Across option

Re: Cubic Spline interpolation - MANY variables at once

Re: ODS Excel - Formatting font for an entire sheet

ODS Excel - Formatting font for an entire sheet

ODS Excel - Proc Report using Across option

Re: Correlate Existing Variables

Re: Correlate Existing Variables

Re: Correlate Existing Variables

Re: Correlate Existing Variables

Correlate Existing Variables

Re: ODS Excel - Tab output based on data

ODS Excel - Tab output based on data

Re: PROC SQL Product Function when Grouping

Re: PROC SQL Product Function when Grouping

Re: PROC SQL Product Function when Grouping

Re: PROC SQL Product Function when Grouping

PROC SQL Product Function when Grouping