PaalNavestad Tracker
https://communities.sas.com/kntur85557/tracker
PaalNavestad TrackerFri, 12 Apr 2024 10:41:40 GMT2024-04-12T10:41:40ZRe: Cumulative sum, moving average,... with CAS actions only
https://communities.sas.com/t5/Developers/Cumulative-sum-moving-average-with-CAS-actions-only/m-p/829605#M6141
<P>Since there is a set of really good cumulative functions in VA I guess there must be a use of the aggregate function. What I really want is the cumulative month to date average for all days in a month and a YTD cumulative for all the days in a year. </P><P> </P><P>Traditionally I've always done these sort of tasks with a datastep. That need the data sorted and retain. But I would love to sse how this have been achieved in VA</P><P> </P><P>Pål N</P>Mon, 22 Aug 2022 06:58:20 GMThttps://communities.sas.com/t5/Developers/Cumulative-sum-moving-average-with-CAS-actions-only/m-p/829605#M6141PaalNavestad2022-08-22T06:58:20ZCumulativePeriod aggregation, can't get this to filter properly
https://communities.sas.com/t5/SAS-Visual-Analytics/CumulativePeriod-aggregation-can-t-get-this-to-filter-properly/m-p/826400#M16291
<P>Hi I have a cumulative period aggregation. When I create an action from a dropdown box to a key value or a table to get the cumulative Month to date this works well. Also if i just a filter directly for a date it works. What i really want is not to have the dropdown but control the filter form the data. I have created a variable that is the date I want and otherwise missing. This does not give me the cumulative aggregation but the days value.</P><P> </P><P>Do anybody have an idea why this should not work.</P><P> </P><P>Pål N </P>Mon, 01 Aug 2022 07:05:16 GMThttps://communities.sas.com/t5/SAS-Visual-Analytics/CumulativePeriod-aggregation-can-t-get-this-to-filter-properly/m-p/826400#M16291PaalNavestad2022-08-01T07:05:16ZRe: Is the SUM function different in VIYA and 9.4
https://communities.sas.com/t5/SAS-Programming/Is-the-SUM-function-different-in-VIYA-and-9-4/m-p/825925#M326228
<P>Thanks, works as intended. I had another fault in my code and got stuck. Bad post by me</P><P> </P>Thu, 28 Jul 2022 14:02:07 GMThttps://communities.sas.com/t5/SAS-Programming/Is-the-SUM-function-different-in-VIYA-and-9-4/m-p/825925#M326228PaalNavestad2022-07-28T14:02:07ZIs the SUM function different in VIYA and 9.4
https://communities.sas.com/t5/SAS-Programming/Is-the-SUM-function-different-in-VIYA-and-9-4/m-p/825907#M326224
<P>Hi I just used a sum function in VIYA (3.5). To my suprise sum(1,.) returns missing. in 9.4 it would return 1. Is the sum function changed in VIYA?</P>Thu, 28 Jul 2022 12:53:45 GMThttps://communities.sas.com/t5/SAS-Programming/Is-the-SUM-function-different-in-VIYA-and-9-4/m-p/825907#M326224PaalNavestad2022-07-28T12:53:45ZRe: Outlier method in CAS. The calculation of percentiles did not converge
https://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814445#M9186
<P>Problem reported as a track. Track number: 7613580754</P><P> </P>Fri, 20 May 2022 14:16:24 GMThttps://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814445#M9186PaalNavestad2022-05-20T14:16:24ZRe: Outlier method in CAS. The calculation of percentiles did not converge
https://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814398#M9185
<P>Thanks taiphe, still do not work. I do not have time to test this using the percentiles method directly. I discussed with local support and create a track so I can attach data and so that SAS can try and see why.</P><P> </P><P>It seems to me that it might have something to do with the number of groups.</P><P> </P><P>And this error is not stopping progress for me. </P><P> </P><P>Pål N</P><P> </P>Fri, 20 May 2022 06:20:32 GMThttps://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814398#M9185PaalNavestad2022-05-20T06:20:32ZRe: Outlier method in CAS. The calculation of percentiles did not converge
https://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814240#M9183
<P>Yes, and from the data I have a challenge with I've run a smaller subset of data and it runs. In the data there are 4 groups that has missing residuals removeing these did not help, and one of them runs. I also have approx. 50 groups that are constant, again removing does not work and running one or two of these does run.</P><P> </P><P>Strange also that zscore runs which I think is a more complex method.</P><P> </P><P>At the moment I'm not that concerned about the method I'm running, I needed the structure to go further. </P><P> </P><P>I think I will report as a bug in the track system, unless omebody from SAS picks tis up with asuggestion.</P><P> </P><P>Thanks for your interest.</P><P> </P>Thu, 19 May 2022 08:52:57 GMThttps://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814240#M9183PaalNavestad2022-05-19T08:52:57ZRe: Outlier method in CAS. The calculation of percentiles did not converge
https://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814220#M9181
<P>Thanks here is the code. Yes I agree the actionset is overall good. Just something about the data. I'm checking for outliers on data coming from a simple exponential forecast from TSModel. There is 469 different by groups all with approx. 880 observations. We run similar workflows on other data and have had no challenges.</P><PRE><CODE class="">proc cas;
session casauto;
dataPreprocess.outlier /
table={name="forecast"
caslib="casuser"
groupby={"unique_id"}
}
inputs={"Error"}
method= "IQR"
treatment = "trim"
outVarsNamePrefix=""
outVarsNameSuffix="excl_Outl"
countOutliers="True"
arguments={ scaleMultiplier=1.5 }
copyVars = {"unique_id" , "ACTUAL", "PREDICT", "ERROR", "prdate"}
casout = {caslib="casuser" name="new_withoutl" replace= true}
casOutOutlierInformation={caslib="casuser" name="OutlierStat" replace=True}
;
run;</CODE></PRE><P>The log writes as this</P><DIV class="">84 proc cas;</DIV><DIV class="">85 session casauto;</DIV><DIV class="">86 dataPreprocess.outlier /</DIV><DIV class="">87 table={name="forecast"</DIV><DIV class="">88 caslib="casuser"</DIV><DIV class="">89 groupby={"unique_id"}</DIV><DIV class="">90 }</DIV><DIV class="">91 inputs={"Error"}</DIV><DIV class="">92 method= "IQR"</DIV><DIV class="">93 treatment = "trim"</DIV><DIV class="">94 outVarsNamePrefix=""</DIV><DIV class="">95 outVarsNameSuffix="excl_Outl"</DIV><DIV class="">96 countOutliers="True"</DIV><DIV class="">97 arguments={ scaleMultiplier=1.5 }</DIV><DIV class="">98 copyVars = {"unique_id" , "ACTUAL", "PREDICT", "ERROR", "prdate"}</DIV><DIV class="">99 casout = {caslib="casuser" name="new_withoutl" replace= true}</DIV><DIV class="">100 casOutOutlierInformation={caslib="casuser" name="OutlierStat" replace=True}</DIV><DIV class="">101 ;</DIV><DIV class="">102 run;</DIV><DIV class="">NOTE: Active Session now casauto.</DIV><DIV class="">ERROR: The calculation of percentiles did not converge. You might want to adjust the tolerance or the maximum number of iterations.</DIV><DIV class="">ERROR: The calculation of percentiles did not converge. You might want to adjust the tolerance or the maximum number of iterations.</DIV><DIV class="">ERROR: The calculation of percentiles did not converge. You might want to adjust the tolerance or the maximum number of iterations.</DIV><DIV class="">ERROR: The calculation of percentiles did not converge. You might want to adjust the tolerance or the maximum number of iterations.</DIV><DIV class="">ERROR: The action stopped due to errors.</DIV><DIV class="">103</DIV><PRE class=""> </PRE><P>Just switching to zscore as the outlier method everything works.</P><P> </P><P> </P>Thu, 19 May 2022 06:48:19 GMThttps://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814220#M9181PaalNavestad2022-05-19T06:48:19ZOutlier method in CAS. The calculation of percentiles did not converge
https://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814010#M9177
<P>Hi I'm using the <SPAN>dataPreprocess.outlier action set in CAS. Using the IQR method i get an error message "ERROR: The calculation of percentiles did not converge. You might want to adjust the tolerance or the maximum number of iterations."</SPAN></P><P><SPAN>I've tried several settings of the tolerance and maxiterations without any success. Since I've never seen this before I can't really create a simple sharable data set.</SPAN></P><P> </P><P><SPAN>The data I'm running with is residuals from a simpel exponential smooth done with proc TSmodel. </SPAN></P><P> </P><P>Changing to method zscore does run the process smoothly.</P><P> </P><P>Does anybody have an idea of why and hat can be done to fix.</P><P>Thanks Pål N</P>Wed, 18 May 2022 08:57:30 GMThttps://communities.sas.com/t5/SAS-Data-Science/Outlier-method-in-CAS-The-calculation-of-percentiles-did-not/m-p/814010#M9177PaalNavestad2022-05-18T08:57:30ZRe: Write missing as '' instead of . in ODS Excel using proc print
https://communities.sas.com/t5/SAS-Programming/Write-missing-as-instead-of-in-ODS-Excel-using-proc-print/m-p/789906#M252846
<P>Thanks a lot I had already gotten the tip and it works. </P>Thu, 13 Jan 2022 07:09:20 GMThttps://communities.sas.com/t5/SAS-Programming/Write-missing-as-instead-of-in-ODS-Excel-using-proc-print/m-p/789906#M252846PaalNavestad2022-01-13T07:09:20ZRe: Write missing as '' instead of . in ODS Excel using proc print
https://communities.sas.com/t5/SAS-Programming/Write-missing-as-instead-of-in-ODS-Excel-using-proc-print/m-p/789904#M252844
<P>Thanks a million Kurt, i knew there was an option but colud not find i. Searched for printmiss. </P><P>I created the excel file as below. (Lot's of code above the get the data in the right way. The challenge here was the spacer column. That could have been solved by creating the data with space culmn as a char column. However I also need blank lines to fit a format, and then the '.' representation would create havoc. Again thanks a lot</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PaalNavestad_0-1642057494924.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/67364iEA7AE2B15DD9BCCE/image-size/medium?v=v2&px=400" role="button" title="PaalNavestad_0-1642057494924.png" alt="PaalNavestad_0-1642057494924.png" /></span></P><P> </P><P> </P><PRE><CODE class="">proc sort data = work.struct_withNbrsSum_trsp;
by group shipper_id;
run;
options missing=" ";
ods excel file = '/shared/winshare/teams_no/SAS_General/subsurface/prod/GGRE_OUT/STFMonthlyShipper/testxlsx.xlsx'
options(sheet_interval="bygroup"
suppress_bylines="yes"
sheet_label=" "
embedded_titles="yes"
embed_titles_once="yes"
);
proc print data=work.struct_withNbrsSum_trsp ;
by group;
run;
ods excel close;</CODE></PRE>Thu, 13 Jan 2022 07:08:05 GMThttps://communities.sas.com/t5/SAS-Programming/Write-missing-as-instead-of-in-ODS-Excel-using-proc-print/m-p/789904#M252844PaalNavestad2022-01-13T07:08:05ZWrite missing as '' instead of . in ODS Excel using proc print
https://communities.sas.com/t5/SAS-Programming/Write-missing-as-instead-of-in-ODS-Excel-using-proc-print/m-p/789894#M252836
<P>Hi, I have to create data in a sepcific form in Excel. I need blank rows and blank columns to conform with some Excel Macros that are using the data. I can easily create the structure, but when I'm outputting the data I get missing represented as . instead of balnk as the macro need.</P><P> </P><P>I'm pretty certain that I seen some tips on this before but can't find them now. </P><P> </P><P>Can somebody please point me in the right direction</P><P> </P>Thu, 13 Jan 2022 06:55:27 GMThttps://communities.sas.com/t5/SAS-Programming/Write-missing-as-instead-of-in-ODS-Excel-using-proc-print/m-p/789894#M252836PaalNavestad2022-01-13T06:55:27ZRe: Calculate Median, P10 and P90 in CAS
https://communities.sas.com/t5/SAS-Programming/Calculate-Median-P10-and-P90-in-CAS/m-p/782191#M249355
Thanks a lot<BR />Wed, 24 Nov 2021 11:33:03 GMThttps://communities.sas.com/t5/SAS-Programming/Calculate-Median-P10-and-P90-in-CAS/m-p/782191#M249355PaalNavestad2021-11-24T11:33:03ZCalculate Median, P10 and P90 in CAS
https://communities.sas.com/t5/SAS-Programming/Calculate-Median-P10-and-P90-in-CAS/m-p/782147#M249328
<P>Hi, i have a 19 million row table for which I need to find the Median, P10 and P90 using several class/groupBy. The table is in CAS. In 9.4 this can easily be done with proc means. From the documentation I see that the percentile methods is not supported in Cas. Moving the table to The Spree server is really slow. I can't find anywhere in the documentation how I can do this in CAS. I tried Fedsql but that do not work?</P><P>Thanks </P>Wed, 24 Nov 2021 07:50:16 GMThttps://communities.sas.com/t5/SAS-Programming/Calculate-Median-P10-and-P90-in-CAS/m-p/782147#M249328PaalNavestad2021-11-24T07:50:16ZRe: FedSQL and Passing Dates in Filter
https://communities.sas.com/t5/SAS-Programming/FedSQL-and-Passing-Dates-in-Filter/m-p/781229#M248995
<P>Hidden is mainly the wrong word. The challenge is that it is rather different from the SQL procedure so an article, or change type log would have been good.</P>Fri, 19 Nov 2021 11:54:42 GMThttps://communities.sas.com/t5/SAS-Programming/FedSQL-and-Passing-Dates-in-Filter/m-p/781229#M248995PaalNavestad2021-11-19T11:54:42ZRe: FedSQL and Passing Dates in Filter
https://communities.sas.com/t5/SAS-Programming/FedSQL-and-Passing-Dates-in-Filter/m-p/781206#M248985
Hi the documentation explain the way dates and time is handled. I had a challenge understanding this.<BR />The way to use a date in a where clause is<BR /><BR />where dateVariable >= date'2015-01-01'<BR />a bit confusing as you tell what it is in front and that there are no () before the date constant.<BR />Fortunatly the date() function worked well in subsets.Fri, 19 Nov 2021 09:34:11 GMThttps://communities.sas.com/t5/SAS-Programming/FedSQL-and-Passing-Dates-in-Filter/m-p/781206#M248985PaalNavestad2021-11-19T09:34:11ZRe: Sgplot reserves large space for textplot
https://communities.sas.com/t5/Graphics-Programming/Sgplot-reserves-large-space-for-textplot/m-p/776200#M22184
<P>Thanks a lot worked like a dream. </P><P>Pål N</P><P> </P>Mon, 25 Oct 2021 14:20:50 GMThttps://communities.sas.com/t5/Graphics-Programming/Sgplot-reserves-large-space-for-textplot/m-p/776200#M22184PaalNavestad2021-10-25T14:20:50ZSgplot reserves large space for textplot
https://communities.sas.com/t5/Graphics-Programming/Sgplot-reserves-large-space-for-textplot/m-p/776181#M22181
<P>Hi, I'm creating a plot where i want to label a point. </P><P>The following program creates data and creates the plot </P><PRE>data t;
do i=0 to 60 by 1;
month=intnx('month','01nov2017'd,i,'b');
v= rand('normal');
if i = 20 then do;
v2=v;
label='I want a text label.............................';
end;
else do;
v2 = .;
label='';
end;
output;
end;
format month date9.;
run;
proc sgplot data=t;
scatter x=month y=v;
text x=month y=v2 text=label/ position=topright;
xaxis display=(nolabel) grid interval=month;
run;</PRE><P>When running the program as above the following appears</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PaalNavestad_0-1635165863695.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/64998i32B5C13DA33A5205/image-size/medium?v=v2&px=400" role="button" title="PaalNavestad_0-1635165863695.png" alt="PaalNavestad_0-1635165863695.png" /></span></P><P>There is large unused space to he right.</P><P>If I run the program with topleft instead I get large unused space to the left instead.</P><P>Running with the position = Center </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PaalNavestad_1-1635166006894.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/64999iDC26C87E4D256B0E/image-size/medium?v=v2&px=400" role="button" title="PaalNavestad_1-1635166006894.png" alt="PaalNavestad_1-1635166006894.png" /></span></P><P>I get unused space both left and right. </P><P> </P><P>My question is:</P><P>Are there any option(s) where I can avoid the large unused space?</P><P>If not is this a bug. It seems strange that the system set aside space that only will be used when the labels are at the end and is completely unnecessary when the labels are in the middle of the plot?</P><P>Thanks </P><P> </P><P> </P><P> </P>Mon, 25 Oct 2021 12:49:44 GMThttps://communities.sas.com/t5/Graphics-Programming/Sgplot-reserves-large-space-for-textplot/m-p/776181#M22181PaalNavestad2021-10-25T12:49:44ZRe: Sum and mean combined in SAS VA
https://communities.sas.com/t5/SAS-Visual-Analytics/Sum-and-mean-combined-in-SAS-VA/m-p/769153#M15316
<P>Thanks a lot. Yes it is confusing mixing sum and mean. However the data is daily averages so you want an average by date and a sum of the departments. This creates a lot of havoc in a lot of reporting and analysis. Most of the use cases is by day or by month. Then you can use sum of month and calculate daily by dividing by days in month. However this do not work in this case as the data is updated daily and you will never know if you have complete months.</P>Wed, 22 Sep 2021 13:11:22 GMThttps://communities.sas.com/t5/SAS-Visual-Analytics/Sum-and-mean-combined-in-SAS-VA/m-p/769153#M15316PaalNavestad2021-09-22T13:11:22ZSum and mean combined in SAS VA
https://communities.sas.com/t5/SAS-Visual-Analytics/Sum-and-mean-combined-in-SAS-VA/m-p/769016#M15308
<P>Hi all I have data that in two different hierarchies.</P><P>Week-date and department sub department.</P><P> </P><P>Example data looks like this</P><P>Week Date Department Sub-department Value</P><P>1 01jan A A1 8</P><P>1 02jan A A1 9</P><P>1 03jan A A1 10</P><P>1 01jan A A2 2</P><P>1 02jan A A2 1</P><P>1 03jan A A2 0</P><P> </P><P>I want to have a cross table that has the week date as rows , department sub-department as columns.</P><P>I want the average by week date and the sum of department sub-department</P><P> </P><P>What I want for collapsed hiearchies</P><P>Week Department</P><P>1 10</P><P>What I get using mean for value </P><P>Week Department</P><P>1 5</P><P>What I get using sum for value </P><P>Week Department</P><P>1 30</P><P> </P><P>When expanding department to sub-department I get the right mean when week-date is collapsed</P><P> </P><P>I have tried several different combination of sum and mean, calculating the number of days and trying table aggregations. </P><P> </P><P>I can solve this by creating an aggregation by week date- department and create a cross tab on this. Then I need to create two crosstabs, one for collapsed department and on for expanded department.</P><P>This is not really elegant. In the real use case I already have several aggregation and joins and really want to avoid to many. </P><P> </P><P>I seem to remember that I had the same challenge in Proc Tabulate back in the late 90's and managed to solve this by a strange combination of crossings. </P><P> </P><P>Is there anybody who has been able to solve this problem without using a separate aggregated table?</P><P> </P><P> </P>Wed, 22 Sep 2021 06:36:16 GMThttps://communities.sas.com/t5/SAS-Visual-Analytics/Sum-and-mean-combined-in-SAS-VA/m-p/769016#M15308PaalNavestad2021-09-22T06:36:16Z