About user24feb

user24feb · ‎08-19-2014

Maybe like this: Data A; Do i=1 To 2; Do j=1 To 12; Date=MDY(j,1,2000+i); Value=Round(Normal(1)*20+50); Output; End; End; Format Date Date9.; Keep Date Value; Run; Proc Expand Data=A Out=B (Keep=Date B_Value); Convert Value = B_Value / Method = None Transformout = (Cmovave 2); Run; Data B; Set B; If Mod(_N_,2) eq 1 Then Do; B_Value=B_Value*2; Output; End; Run;

user24feb · ‎08-14-2014

This code is less sophisticated than RW9's code because it requires a dummy variable, but it might do the job: Data A; Input order period $ area $ sales comm; Datalines; 1 jan north 45 25 1 feb north 44 44 1 Mar north 75 25 1 apr north 41 35 1 may north 65 55 1 jun north 45 15 2 jan South 45 25 2 feb South 44 44 2 Mar South 75 25 2 apr South 41 35 2 may South 65 55 2 jun South 45 15 3 jan East 45 25 3 feb East 44 44 3 Mar East 75 25 3 apr East 41 35 3 may East 65 55 3 jun East 45 15 4 jan West 45 25 4 feb West 44 44 4 Mar West 75 25 4 apr West 41 35 4 may West 65 55 4 jun West 45 15 ; Run; Proc Sort Data=A; By Order Period Area; Run; Proc Transpose Data=A Out=A_Vector; By Order Period Area; Var Sales Comm; Run; Data A_Vector; Set A_Vector; Area_Var=Compress(Area!!"_"!!_NAME_); Run; Proc Sort Data=A_Vector; By Period; Run; Proc Transpose Data=A_Vector Out=A_Trans (Drop=_NAME_); By Period; Var Col1; ID Area_Var; Run;

user24feb · ‎08-14-2014

!! is a concatenating operator. I use it instead of vertical bars (||). The reason is, that my keyboard does not have a solid vertical bar, so I use either two broken vertical bars (¦¦) or two exclamation marks (!!).

user24feb · ‎08-14-2014

Sure, you can, but I would not use a comma as a separating character: %Macro Links(ClientID=); /* maybe work on a list .. */ %Let i=1; %Let ID=%Scan(&ClientID.,1,' '); %Do %While (&ID^=); %Put **&ID.**; %Let i=%Eval(&ID.+1); %Let ID=%Scan(&ClientID.,&i.,' '); %End; %Mend; %Links(ClientID=1 2 3 4);

user24feb · ‎08-14-2014

I would prefer the Phone_Text-version which transforms the character to numeric over the Phone_Text2-version: Data A; Input Phone $; Phone_Text=Put(Input(Phone,Best11.),Z11.); Phone_Text2=Repeat('0',10-Length(Phone))!!Phone; /* use a Macro-Variable instead of 10 */ Datalines; 9167642524 9167642525 9167642526 642526 ; Run;

user24feb · ‎08-11-2014

Thanks for your quick reply. .. if I put the where part to the having part (and remove where completely) it still gives a weird result. Any ideas what is wrong with this code? Proc SQL; Create Table Daten.Query_1_Test_B As Select Distinct Plant_Combined, KeyDate_Month_Key, MaterialClass_key, Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3 From sqlserv.vFact_SalesOrder_DP Group By Plant_Combined, MaterialClass_key, KeyDate_Month_Key Having KeyDate_Month_Key in (201401 201402 201403) AND MaterialClass_key="JGG" AND Substr(Plant_Combined,1,6) in ("[0031]" "[0032]" "[0034]" "[0332]") AND IsFutureDeliveryDate eq 1 AND Ultimo_Filter_Reporting eq 1 Order By KeyDate_Month_Key, Plant_Combined, MaterialClass_key ; Quit;

user24feb · ‎08-11-2014

I am trying to summarize data using proc sql. If I use this subquery, it gives me the desired result: Proc SQL; Create Table Daten.Query_1_Test_C As Select Plant_Combined, KeyDate_Month_Key, MaterialClass_key, Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3 From (Select * From sqlserv.vFact_SalesOrder_DP Where KeyDate_Month_Key in (201401 201402 201403) AND MaterialClass_key="JGG" AND Substr(Plant_Combined,1,6) in ("[0031]" "[0032]" "[0034]" "[0332]") AND IsFutureDeliveryDate eq 1 AND Ultimo_Filter_Reporting eq 1) Group By Plant_Combined, MaterialClass_key, KeyDate_Month_Key Order By KeyDate_Month_Key, Plant_Combined, MaterialClass_key ; Quit; What I cannot understand though, is why this is not the same as: Proc SQL; Create Table Daten.Query_1_Test_B As Select Distinct Plant_Combined, KeyDate_Month_Key, MaterialClass_key, Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3 From sqlserv.vFact_SalesOrder_DP Where IsFutureDeliveryDate eq 1 AND Ultimo_Filter_Reporting eq 1 Group By Plant_Combined, MaterialClass_key, KeyDate_Month_Key Having KeyDate_Month_Key in (201401 201402 201403) AND MaterialClass_key="JGG" AND Substr(Plant_Combined,1,6) in ("[0031]" "[0032]" "[0034]" "[0332]") Order By KeyDate_Month_Key, Plant_Combined, MaterialClass_key ; Quit; Shouldn't "where" and "having" do the job of a subquery?

user24feb · ‎08-08-2014

Hello, I am trying to access data through SAS Access to ODBC using the little test program below: Libname sqlserv ODBC Schema=DBO DSN=sassql; Libname Daten "C:\Users\MyName\Desktop\xxx\Daten"; Proc SQL NoPrint; Create Table Daten.Query_1_Test As Select Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3, KeyDate_Month_Key From sqlserv.vFact_SalesOrder_DP Where KeyDate_Month_Key in (201401) Group By KeyDate_Month_Key Order By KeyDate_Month_Key ; Quit; The problem is that it yields different results when I run it at different times. The code runs without errors. The data is historical data. Is there a way to check if the SQL-procedure timed out or caused a different error that does not show in the log during execution?

user24feb · ‎08-07-2014

does "SAS/Graph" appear in the log if you run proc setinit; run;

user24feb · ‎08-05-2014

Use Format for Proc Print and Put to create a String: Data A; Input Phone; Format Phone Z11.; Phone_Text=Put(Phone,Z11.); Datalines; 9167642524 9167642525 9167642526 ; Run;

user24feb · ‎07-31-2014

Just an idea: DATA exam; input id $ e1-e4; DATALINES; Start 30 15 20 45 End 35 16 25 42 ; RUN; Proc Sort Data=exam; By id; Run; Data Start (Keep=Value Rename=(Value=Start)) End (Keep=Value Rename=(Value=End)); Set exam; Array N _NUMERIC_; By ID; Do over N; Value=N; ID=id; If ID eq "Start" Then Output Start; Else Output End; End; Run; Data exam_Trans; Merge Start End; Run; Proc Print Data=exam Noobs; Run; Proc Print Data=exam_Trans Noobs; Run;

user24feb · ‎07-30-2014

I might be entirely wrong, but I think: %Let Periods=3; %Let Lead=5; %Let Multiplier=12; /* 12 months */ %Let Comb_Lag=%Eval(&Periods.*&Multiplier.); Data A (Drop=i j k); Format Date Date9.; Do k=1 to 3; Do j=1 to 5; Do i=1 to 12; Date=MDY(i,1,j+2000); ACTUAL=Round(Normal(1)*k+20); /* k as Standard Deviation */ ID=k; Output; End; End; End; Run; /* "simple" seasonal (??) moving average */ Data A_Forecast (Keep=ID Date ACTUAL Mov_Ave); Set A; By ID; Array dummy{*} dummy1-dummy12; Array dummy_sum{*} dummy_sum1-dummy_sum12; Array dummy_drop {*} dummy_drop1-dummy_drop12; Retain dummy_sum1-dummy_sum12; Do i=1 To 12; If Month(Date) eq i Then Do; dummy{i}=ACTUAL; dummy_drop{i}=Lag&Comb_Lag.(ACTUAL); End; End; If First.ID Then Do; count=0; Do i=1 To 12; dummy_sum{i}=0; End; End; count+1; If count gt &Comb_Lag. Then Do; Do i=1 To 12; dummy_sum{i}=Sum(dummy_sum{i},dummy{i},-dummy_drop{i}); End; End; Else Do; Do i=1 To 12; dummy_sum{i}=Sum(dummy_sum{i},dummy{i}); End; End; If count ge &Comb_Lag. Then Do; Do i=1 To 12; If not Missing (dummy{i}) Then dummy_sum_act=dummy_sum{i}; End; Mov_Ave=dummy_sum_act/&Periods.; End; Run; /* fill in lead */ Data A_Forecast_Lead (Drop=i); Retain Date ID Mve_Ave; Set A_Forecast; By ID; If Last.ID Then Do; Output; Do i=1 to &Lead.; Date=IntNX('month',Date,1,'same'); ACTUAL=.; Output; End; End; Output; Run;

user24feb · ‎07-30-2014

p.s. credit goes to SAS 🙂 25027 - Compute the moving average of a variable

user24feb · ‎07-30-2014

Proc expand is rather used to transform data than to use it for forecasting. If you#re actually looking for simple moving averages (not exponentially weighted ones) you could use a data step. Maybe something like this: Data A; Input Date:Date9. ACTUAL; Format Date Date9.; Datalines; 01JAN2000 23 01FEB2000 45 01MAR2000 12 01APR2000 89 01MAY2000 23 ; Run; Data A_Forecast (Drop=dummy); Retain dummy; Set A; dummy=Sum(dummy,ACTUAL,-Lag3(ACTUAL)); Mov_Ave_3_GD=dummy/3; Run;

user24feb · ‎07-29-2014

Do you mind posting the sparse data as well (%sasmpsxs doesn't work)?

Online Status	Offline
Date Last Visited	‎09-02-2022 10:58 AM

Re: Macros

Conditionally define a constraint with proc optmodel

Re: Define Constraint for Proc Optmodel

Define Constraint for Proc Optmodel

Re: data set for "simulation models .." by Erdman

data set for "simulation models .." by Erdman

Re: Saving Excel-dataset after modifying

Saving Excel-dataset after modifying

Re: Create and slice a sparse set of parameters in proc optmodel

Create and slice a sparse set of parameters in proc optmodel

Re: Conditionally define a constraint with proc optmodel

Re: Define Constraint for Proc Optmodel

Re: data set for "simulation models .." by Erdman

Re: PROC EXPORT to XLSX is creating a .bak file

Re: Saving Excel-dataset after modifying

Re: Passing a comma delimited variable to a Macro

Re: log summary is blank

Re: import excel from , skipping first 7 lines

Re: data set for "simulation models .." by Erdman

Re: Splitting a macro variable string into 2

Re: Grouping dates by bi-monthly groups

Re: Transpose of Data

Re: How to print a numeric SAS variable with leading zeros?

Re: Macro Parameters

Re: How to print a numeric SAS variable with leading zeros?

Re: Summarizing with Proc SQL

Summarizing with Proc SQL

SAS ACCESS to ODBC/Proc SQL

Re: SAS Dashboard

Re: How to print a numeric SAS variable with leading zeros?

Re: transposing a data set

Re: Moving average forecast

Re: Moving average forecast

Re: Moving average forecast

Re: binary variables with price in objective function