I have the following two tables: Persons
Obs ID LName FName Address City State Volunteer
1 1 County John 213 st Lincon CA V
2 2 Surrey ? 715 st Carson City NV
3 Michael 38o M St Truckee Ca
4 678 ?
5 25 Harvey Elizabeth
6 114 Rabipour 2725 Loyala NV
Th other tables - Donate
Obs ID AmtPaid Volunteer LName FName Address City State
1 25 50 Harvey Elizabeth
2 489 . V Timothy
3 894 250 Rollo Michele 34 Lake Rd#5 Tahoe City CA
4 489 75
5 678 50
6 114 . Rabipour 2725 Loyola NV
7 25 100 Harvey Elizabeth
8 894 50 Rollo Michele 34 Lake Rd#5 Tahoe City CA
My workers are trying to make a new data step to see a report which would show:
1)the total AmtPaid received from each person having a specific ID in both tables.
2) We want to compute the total AmtPaid by ID, and combine the totals with the person data.
3)Then print the data set showing only the ID, first and last name, and total donations for each friend.
4) And want then to print the data set showing only the ID, first and last name, and total donations for each friend.
I have computed in R , but as SAS is new in our company , need to see how it can show the results.Can somebody guide us.
Looking at the two tables, I noticed that:
In Persons table there is one (obs=3) with a missing ID.
In case of more missing IDs, it may cause difficulties.
Are all IDs unique ? - If negative - what is the explanation for it ?
To get total AmtPaid by ID use proc Means:
1) proc means data= < donate table> NWAY noprint missing;
class ID;
var AmtPaid;
ID LName FName; /* assuming unique per ID, ready for the report */
output out=totals_paid sum=amt_paid;
run;
proc sort data=totals_paid; by ID /* or by LName FName */; run;
2) I see no difference between query 3 and 4 - they ask the same ?!
Alternatively you can do it by sql:
proc sql;
create tabel totals_paid as
select ID, LName, Fname,
sum(AmtPaid) as amt_paid
FROM < donate table>
Group by ID
order by id; /* or: by LName, FName - to be alphabetically */
quit;
Finaly, to print the report use PROC PRINT:
proc pribt data = totals_paid /* optional: NOOBS */ ;
var ID LName FName amt_paid;
run;
What is difference between 3) and 4) ?
data persons;
infile cards truncover;
input Obs ID ( LName FName Address City State Volunteer) (& $20.);
cards;
1 1 County John 213 st Lincon CA V
2 2 Surrey ? 715 st Carson City NV
3 3 . Michael 38o M St Truckee Ca
4 678 ?
5 25 Harvey Elizabeth
6 114 Rabipour 2725 Loyala NV
;
data Donate;
infile cards truncover;
input Obs ID AmtPaid (Volunteer LName FName Address City State) (& $20.);
cards;
1 25 50 . Harvey Elizabeth
2 489 . V Timothy
3 894 250 . Rollo Michele 34 Lake Rd#5 Tahoe City CA
4 489 75
5 678 50 .
6 114 . . Rabipour 2725 Loyola NV
7 25 100 . Harvey Elizabeth
8 894 50 . Rollo Michele 34 Lake Rd#5 Tahoe City CA
;
run;
proc sql;
/** 1) **/
create table first as
select id,sum(AmtPaid) as total
from donate
where id in (select distinct id from persons);
/** 2) **/
create table second as
select a.*,total
from persons as a,first as b
where a.id=b.id ;
/** 3) **/
select id,fname,lname,sum(total) as total_donate
from second
group by fname,lname;
quit;
Sure. But you didn't post the output yet .
data persons(index=(id));
infile cards truncover;
input Obs ID ( LName FName Address City State Volunteer) (& $20.);
cards;
1 1 County John 213 st Lincon CA V
2 2 Surrey ? 715 st Carson City NV
3 3 . Michael 38o M St Truckee Ca
4 678 ?
5 25 Harvey Elizabeth
6 114 Rabipour 2725 Loyala NV
;
data Donate(index=(id));
infile cards truncover;
input Obs ID AmtPaid (Volunteer LName FName Address City State) (& $20.);
cards;
1 25 50 . Harvey Elizabeth
2 489 . V Timothy
3 894 250 . Rollo Michele 34 Lake Rd#5 Tahoe City CA
4 489 75
5 678 50 .
6 114 . . Rabipour 2725 Loyola NV
7 25 100 . Harvey Elizabeth
8 894 50 . Rollo Michele 34 Lake Rd#5 Tahoe City CA
;
run;
/*1)*/
data temp;
ina=0;inb=0;
merge donate(in=ina) persons(in=inb keep=id);
by id;
if ina;
run;
proc summary data=temp;
by id;
var AmtPaid;
output out=first sum=total;
run;
/*2)*/
data second;
merge persons(in=ina) first(in=inb);
by id;
if ina and inb;
run;
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!
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.