data have; infile cards expandtabs truncover; input AccountID Month :yymmdd10. (LoanAmount Balance) (:dollar12.); format month yymmd7.; cards; 1 2022-01-01 $10,000 $9,000 1 2022-04-01 $10,000 $8,500 1 2022-08-01 $10,000 $7,500 1 2022-12-01 $15,000 $10,000 2 2022-01-01 $5,000 $5,000 2 2022-04-01 $5,000 $5,000 2 2022-08-01 $5,000 $5,000 2 2022-12-01 $5,000 $5,000 ; proc sql; create table want as select AccountID,Month format=year4.,LoanAmount format=dollar20., sum(LoanAmount) as total_LoanAmount format=dollar20., sum(Balance) as total_Balance format=dollar20. from have group by AccountID having Month=max(Month); quit; ... View more

if  Your data are sorted chronologically MONTH is stored as a sas date variable, always taking the 1st day of the indicated month, then: data want; set have; if _n_=1 then do; declare hash h (dataset:'have(obs=0)'); h.definekey(all:'Y'); h.definedone(); end; h.add(); if h.check(key:location,key:make,key:model,key:color,key(intnx('month',month,-1))=0 then sold_same_model_last_month='Yes' else sold_same_model_last_month='No-' run; Of course, in the absence of sample data in the format of a working DATA step, this code is untested. ... View more

But that is IS what your current code is doing. You are passing the string '27OCT2022'd as the value of the new_date_field parameter of your %DATA_PULL() macro.     Note that the call is passing string literals as the values of the other two "date" parameters, instead of a date literal like the last parameter.  If you needed to convert those strings into date values you would need to include an INPUT() function call in your code.  For example to calculate how many days from start to end you would have to use something like: date_range=input(&end_dt,yymmdd10.) - input(&start_dt,yymmdd10.);  Which will work because you included the quotes into the values of START_DT and END_DT .   NOTE: The macro call is much clearer if you include the parameter names in the call, especially if the macro has more than one parameter. %data_pull (yyyy=2022 ,start_dt='2022-10-27' ,end_dt='2022-10-31' ,new_date_field='27OCT2022'd ); SAS does not care if you defined the macro to allow the values to be passed by position or not.  You can still pass them by name when you call the macro.  You just cannot do the reverse: define the macro to require the values to be passed by name and then attempt to call it with the values passed by position.   ... View more

Try this: data number2date; n = 201603 ; format d date11.; d = MDY(mod(n,100),1,n/100); run;   Bart ... View more

Thank you!   This step below worked as well.   proc sort data=tableB out=b_unique nodupkey; by customerID; run; proc sort data=tableA; by customerID; data tableC; merge tableA(in=ina) b_unique(in=inb drop=deleteflag); by customerID; if not inb; run; ... View more

@Sas_noob25 wrote: Thank you, both of these worked out for me. in the intnx function, what is the 's' doing at the end? Read the documentation.    'alignment' controls the position of SAS dates within the interval. You must enclose alignment in quotation marks. Alignment can be one of these values: BEGINNING, MIDDLE, END, and SAME. SAME specifies that the date that is returned has the same alignment as the input date. For more information, see SAS DS2 Language Reference. controls the position of SAS dates within the interval. You must enclose alignment in quotation marks. Alignment can be one of these values: BEGINNING specifies that the returned date or datetime value is aligned to the beginning of the interval. Alias B MIDDLE specifies that the returned date or datetime value is aligned to the midpoint of the interval, which is the average of the beginning and ending alignment values. Alias M END specifies that the returned date or datetime value is aligned to the end of the interval. Alias E SAME specifies that the date that is returned has the same alignment as the input date. Aliases S SAMEDAY See SAME Alignment Default BEGINNING Data type CHAR, NCHAR, NVARCHAR, VARCHAR See Aligning SAS Date Output within Its Intervals ... View more

Create a format to define which states are internal and which external: proc format lib=work; value $statetyp 'CA' = 'EX' 'FC' = 'US' 'GB' = 'EX' ... /* correct mistakes */ otherwise = 'er' /* as error to be checked */ ; run; then summarize using the format: proc means data=have; class state; format state statetyp. ; var <list of variables to sum> ; output out=want sum=; run; ... View more