Dear all,
I have a data set which looks like in the attached file, and I need the output file as in the attached (other small table). I have tries using many ways but could not get the expected output.
Kindly help.
Thank you!
please try
proc sort data=have;
by manager date;
run;
data want;
set have;
by manager date;
retain count tot;
if first.date then do;
count=1;
tot=total;
end;
else do;
count=count+1;
tot=tot+total;
end;
if last.date;
drop staff total;
run;
alternatively by proc sql
proc sql;
create table want as select manager, date, count(staff) as count, sum(total) as tot from have
group by manager,date;
quit;
please try
proc sort data=have;
by manager date;
run;
data want;
set have;
by manager date;
retain count tot;
if first.date then do;
count=1;
tot=total;
end;
else do;
count=count+1;
tot=tot+total;
end;
if last.date;
drop staff total;
run;
alternatively by proc sql
proc sql;
create table want as select manager, date, count(staff) as count, sum(total) as tot from have
group by manager,date;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.