topic Re: Merge in SAS Programming

Merge

afs — Sat, 12 Nov 2016 02:05:08 GMT

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.

Re: Merge

Shmuel — Sat, 12 Nov 2016 03:15:06 GMT

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;

Re: Merge

azee007 — Sat, 12 Nov 2016 05:15:34 GMT

Thanks it worked with some changes as per my data...You really explained well. thanks again ...

Re: Merge

Ksharp — Sat, 12 Nov 2016 06:09:47 GMT

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;

Re: Merge

afs — Sun, 13 Nov 2016 03:08:08 GMT

can we complete the code without using proc sql?

Re: Merge

Ksharp — Sun, 13 Nov 2016 04:08:56 GMT

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;

Re: Merge

afs — Mon, 14 Nov 2016 03:39:02 GMT

i have used the first option
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;
But if i have data with some missing values that they have same IDs lets suppose 25 and have paid 4 times but the name of ID 25 is mentioned in the obs where the person have not paid any things so it is missing. When i run teh code , the SAS is not reading teh name from the missing Amt_paid. Can I get the names read from missing Amt Paid observations , so that no name is missed .