I only want to keep the last record of each name, how can I deal with it? Should I actually use the array function or sth else?
Sort by name and dateofsales, then do
data want;
set have;
by name;
if last.name;
run;
I need to perform the tasks within the same DATA step.
@user1029 wrote:
I need to perform the tasks within the same DATA step, and after I add the code into my SAS coding, it displays an error.
I don't understand why it has to be in the same data step. Above, @Kurt_Bremser did not say to add it into your existing DATA step.
But anyway, when someone says "it displays an error" but doesn't show us the code they are using or what the error is ... it's really impossible to help you. Please show us the LOG so we can see both the code and the error.
Run the data step with
by name notsorted;
Retain a variable to keep the max of dates, and another for the sum:
retain max_date sum_sales;
if first.name
then do,
max_date = .;
sum_sales = 0;
end,
max_date = max(max_date,salesdate);
sum_sales + sales;
At last.name, calculate the date difference and OUTPUT.
@user1029 Looks like you and @Cheggg1111 got the same homework.
Appreciate that you've given that first a try on your own and shared your code with the data step creating the sample data included.
Below something that should work for you.
data have;
infile datalines dsd truncover;
input name :$20. dateOfSales :date11. SalesAmount;
datalines;
David Wong,1/Sep/2021,13200
Brian Leung,15/Sep/2021,23450
Brian Leung,10/Sep/2021,33000
Mary Chan,3/Sep/2021,45600
Mary Chan,20/Sep/2021,37800
Mary Chan,1/Aug/2021,21500
Mary Chan,30/Aug/2021,42000
John Tam,12/Sep/2021,35000
;
proc sort data=have;
by name dateOfSales;
run;
data want;
set have;
by name dateOfSales;
totalSales+SalesAmount;
if last.name then
do;
salesName=name;
format latestDate ddmmyyS10.;
latestDate=dateOfSales;
salesDays=dateOfSales - '30/Sep/2021'd;
output;
call missing(totalSales);
end;
keep salesName latestDate salesDays totalSales;
run;
data have; infile datalines dsd truncover; input name :$20. dateOfSales :date11. SalesAmount; datalines; David Wong,1/Sep/2021,13200 Brian Leung,15/Sep/2021,23450 Brian Leung,10/Sep/2021,33000 Mary Chan,3/Sep/2021,45600 Mary Chan,20/Sep/2021,37800 Mary Chan,1/Aug/2021,21500 Mary Chan,30/Aug/2021,42000 John Tam,12/Sep/2021,35000 ; proc sql; create table want as select name,max(dateOfSales) as LastSaleDate,sum(SalesAmount) as TotalSales, '30Sep2021'd-calculated LastSaleDate as daysbetween from have group by name; quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.