Are there any other variables? I ask because the data set you create has the possibility of implying precision for other variables, or your variables combined with the one shown, that would very likely be false.   I would be interested in what the resulting data set is to be used for. I may be that you think you need to dis-aggregate the values you have when use of other options may allow use of the existing data set.   For what little it may be worth you may want to consider using numeric 1  and 0 values instead of text "yes" and "no". The sum of a 1/0 coded variable is the number of '1' values, the mean is a decimal percentage and some other tricks involving "any" "all" or "none" of the 1 and 0 values can be done with the functions max, min, and range that involve moderately ugly programming with character 'yes' and 'no'. ... View more

What are YOUR rules for begin and end of month? I haven't run into anything that has "month" start or end that depends on the day of the week. So you implement your rule(s) after defining them.   The only common rules involve the date to count weeks from the start of year because the day of the week a year starts will vary from year to year for some purposes hence the U, V and W parameters available for WEEK functions. But the Year still starts on 1 January. The months still start on the first day of the month.   There DATDIF and YRDIF functions that are available for certain financial things that use a "basis", in this case a parameter of the function, such as '30/360' that calculates difference between dates using a 30 day\interval and arbitrary 360 day year. But that function is for between two dates not changing start or end of existing calendar calendar elements like "start of month" ... View more

Another option is to save the XLSX as a CSV file and read that with a data step, best, or import the CSV. Generally the Excel format applied to the date field will be the result in the CSV and SAS will recognize most of those. And in the case of the mixed numeric/character values the character date that would export from Excel as "9/25/2024" will also generally be read by SAS correctly. A corresponding date format is then typically applied by Proc Import. ... View more

I would suggest contacting the source where you heard of that file, or the author of any paper that might might reference it.   Since that file name contains what might be partial date information there might be other versions with other date portions in the name somewhere but the only reference I find is in a spread sheet at https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://pages.stern.nyu.edu/~jwurgler/data/Investor_Sentiment_Data_20220821_POST.xlsx&ved=2ahUKEwiPv-amt-SKAxVXEDQIHXJtIPAQFnoECBgQAQ&usg=AOvVaw3le5zjgLf2I6rLyh8ceo_- which apparently has a "conversion" to STATA for a different period of data. ... View more

What kind of model? Some of the time-series procedures are set up to handle such internally. I suggest providing much more detail about what you are attempting. I mention time-series because your question included "Do the original dates need to be modified as the dates" and there is no indication as to where such dates might reside. I also think that your "example" is incorrect. I would expect something more like:   Obs X Y Lag1_Y 1 x1 y1   2 x2 y2 y1 3 x3 y3 y2 4 x4 y4 y3 5 x5 y5 y4 6 x6 y6 y5   Also if there are ANY other variables in your model then how they are used might have an impact on how the Lags are calculated. For example if one variable indicates a geography, product or other grouping variable (typically a CLASS variable in SAS) then you likely do not want the lagged value from a different group to be included.   @mike111111 wrote: I am new to SAS and have a question for help. Here is the simplified question.   I am looking X effect on Lagged Y, let's say Lag1_Y=k*X +c. There are total 6 observations of "X" and "Y".   Obs X Y Lag1_Y 1 x1 y1   2 x2 y2 y2 3 x3 y3 y3 4 x4 y4 y4 5 x5 y5 y5 6 x6 y6 y6   I lagged variable "Y" and get 5 observations for "Lag1_Y".   Now the effective observation pairs are 5:   1    x1 y2 2   x2 y3 3   x3 y4 4   x4 y5 5   x5 y6   How should I use the lagged variable in model? Do the original dates need to be modified as the dates shown in lower portion before regression analysis? I appreciate your help in advance      ... View more

The only method I have had any luck with maintaining Excel values is to export the sheets to CSV file(s) and then use a data step to read the resulting files setting the values that Excel likely had as numeric as character to maintain the leading 0 in SAS.   If the sheets have the same structure then read the separate files by changing the infile file name and the Data set created. ... View more

I will admit that there aren't good examples in the documentation. But the numericl collition option is for PROC SORT, not the OPTIONS statement AFAIK. The SORTSEQ option on the Options statement is basically for Language character set such as the sort order differences for "A" "a" "â" using a specific language. French might sort the "â" differently than Vietnamese for example.   proc sort data=have out=foo1 sortseq=LINGUISTIC(numeric_collation=on); by name; run; ... View more

My likely solution to this would look more like: proc format; invalue mydate '01JAN1900'=.N other=[date9.]; value mydate .N='Date not exist' other=[ddmmyy10.]; run; data have; format ddate ddmmyy10.; input ddate : mydate.; format ddate mydate.; cards; 01JAN1900 01JAN1900 15DEC2023 16JAN2024 07SEP2021 ; Run; This use of a special missing from the custom informat would differentiate the result from any other malformed or missing "date" input. The custom format would also let you know that other missing "dates" were not the special original value and could be handled differently when needed.   Special missing such as the .N (or any of the other .letter or ._) can be tested in comparisons such as : if ddate = .N then <do something that might differ from simple missing>; but the values are still missing for purposes like calculations.   ... View more

Why do you specify that Proc SQL has to be used? I suspect that Proc Summary will generate the needed result in one pass. Select the appropriate _TYPE_ value if you need them split out.   Consider this example. You should have the SAS supplied data set SASHELP.CARS in your installation so the code would run and generate output. Proc summary data=sashelp.cars; class origin make type; var msrp; output out=work.summary sum=; run; When you examine the output data set, WORK.SUMMARY, the summary will have the sum of the variable MSRP by overall, each level of the variables Origin, Make and Type alone and in combinations of 2 or 3 at a time the. Variable _TYPE_ can be used to subset the result as needed. IF you only need specific values of any of the Class variables you can use a WHERE clause to restrict them but the way you asked your question is not clear whether the "lists" are specified or if you want all the values  that may change. An additional advantage of Proc Summary over Proc SQL is if you need other variables summarized you just add the variable to the VAR statement. Also if additional statistics are needed, such as Min and Max, you can add the option /autoname to the output line and SAS will create variable names with the statistic appended. With Proc SQL you would have to write some possibly very complex code to create variable names. Example requesting different statistics for different variables Proc summary data=sashelp.cars; class origin make type; var msrp invoice horsepower weight ; output out=work.summary2 sum(msrp invoice) = min(horsepower weight)= max(horsepower weight)= mean(horsepower weight)= /autoname ; run; ... View more

Some things to consider other than the size of the data set: What kind of values are in the BY variables? Some of  the options available in SORTSEQ, such as LINGUISTIC and/or NUMERIC_COLLATION may make Proc sort preferred over some simpler approaches because the desired result order may be attainable using them but not in other tools.   For example with the NUMERIC_COLLATION you can get text values that include characters to include the numeric value of the numerals and not the character coding only. This means for example that in sorting street addresses where you have a name and number that you get 11  Main St 45 Main St 123 Main St 145 Main St 1150 Main St in that order instead of 11 Main st 1150 Main St 123 Main St 145 Main St 45 Main St ... View more

Something that you may find useful: The CONSTANT function. Instead of typing out many digits you can use the CONSTANT('PI') and not have to worry about typo's. Also this function tells someone reading the code what you are using. There are other constants such as E and Euler's constant plus somewhat system dependent values BIG and SMALL plus log and square root of the BIG and SMALL.   ... View more

Numeric variable not a date value. 7 digits. So which of 2020401 is the "month". With out a very clear definition of what that variable YYYYMM represents and how I wouldn't touch of this with code.   @Ronein wrote: Hello For each customer I have 12 rows (12 months data) with information of current account balance. I want to calculate for each customer:  Maximum number of months with negative balance in a row (It means that month after month had negative balance). In this example: Customer 111 has 3 months in row with negative balance Customer 222 has 0 months in row with negative balance What is the way to calculate it in sas please ? Please see a code that calculate it well but maybe there is other nice code  ? data have; input CustID YYYYMM balance; IF balance<0 then Ind_Neg=1;else Ind_Neg=0; cards; 111 2020401 1000 111 2020402 -4000 111 2020403 1000 111 2020404 1900 111 2020405 -1000 111 2020406 -2000 111 2020407 5000 111 2020408 6000 111 2020409 -2000 111 2020410 -3000 111 2020411 -4000 111 2020412 8000 222 2020401 3000 222 2020402 4000 222 2020403 1000 222 2020404 5000 222 2020405 8000 222 2020406 7800 222 2020407 5000 222 2020408 3000 222 2020409 1000 222 2020410 2000 222 2020411 1000 222 2020412 7000 ; Run; proc sort data=have; by CustID YYYYMM; run; data want1; set have; by CustID; retain Accum_Nr_Neg; if first.CustID then Accum_Nr_Neg=Ind_Neg; else Accum_Nr_Neg+Ind_Neg; run; data want2; set have; by CustID; retain Accum_Nr_Neg_in_Row; if first.CustID then Accum_Nr_Neg_in_Row=Ind_Neg; else Accum_Nr_Neg_in_Row=sum(Accum_Nr_Neg_in_Row,Ind_Neg)*Ind_Neg; run; proc sql; create table want3 as select CustID, max(Accum_Nr_Neg_in_Row) as max_Accum_Nr_Neg_in_Row from want2 group by CustID ; quit;   ... View more

Best practice on this forum is any time you get a message in the log that you want to question that you provide the code and the message from the log. Copy the text from the log starting at the first line of the data step or procedure through the last line of all the messages. Then on the forum open a text box on the forum using the </> icon that appears above the main message window and paste the text and paste the text. As a minimum that removes any question as to the exact code that was submitted. An example providing what should be a valid value for the WtHr variable, if maybe not biologically plausible shows: 1 data example; 2 Wthr = 1; 3 Ecc=sqrt(1-WtHR/3.14159)**2); - 388 200 76 ERROR 388-185: Expecting an arithmetic operator. ERROR 200-322: The symbol is not recognized and will be ignored. ERROR 76-322: Syntax error, statement will be ignored. 4 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.EXAMPLE may be incomplete. When this step was stopped there were 0 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.03 seconds Which clearly shows the code you posted is an error and SAS will not do much of anything in the way of output for ECC or any dependent variables.           ... View more

This part of your log: Output Added: ------------- Name: ResidualNormalityPanel Label: Residual Normality Panel Template: ets.arima.Graphics.NormalityPanel Path: Arima.Estimate.ResidualNormalityPanel ------------- Looks like the ods object  you want is the same as @sbxkoenk's code used. Perhaps you can show the Log where you have the ODS OUTPUT statement with your model.   proc arima data=series_31; identify var=Yt(1) crosscorr=(X1t(1) X2t(1) X3t(1)) noprint; estimate q=1 input=( (1) X1t (1) X2t (1) X3t) method=ml; ODS OUTPUT ResidualNormalityPanel= work.yourdatasetname ; run; quit; I don't have access to Proc Arima so can't run the code. There might be that with procedures supporting run group processing like Proc Arima that the location of the ODS OUTPUT has to be within the run group.       ... View more

@sasalex2024 wrote: Dear Koen, Thank you very much for your response. Your code works perfectly for the example model you provided. However, I have been unable to adapt it to my specific case, which involves extracting residuals from a model with inputs and ARMA errors. I tried including the lines: *ods trace on; ods trace off; ODS output ResidualNormalityPanel=work.abc; before my PROC ARIMA code and ending with a quit; after the last run;. Unfortunately, this did not produce the dataset work.abc as expected. Would you be able to clarify if additional modifications are needed to make this approach work for models with inputs and ARMA errors? Thank you once again for your time and assistance! Run the trace on/off around your model code and show us the LOG. The log will include a bunch of bits with Names of the ODS objects created, include all of that along with the code and other messages the procedure generates. It may be that your install created a different ODS object than the ResidualNormalityPanel, or the spelling changed, from @sbxkoenk's install.   ODS TRACE ON; proc arima data=a; identify var=Yt crosscorr=(X1t X2t) noprint; estimate q=1 input=( (1) X1t (1) X2t ) method=ml; run; quit; ODS TRACE OFF;   ... View more