DATA Step, Macro, Functions and more

Merge

Reply
Contributor afs
Contributor
Posts: 28

Merge

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.

 

Trusted Advisor
Posts: 1,384

Re: Merge

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;

       

 

Contributor
Posts: 34

Re: Merge

Thanks it worked with some changes as per my data...You really explained well. thanks again ...
Contributor afs
Contributor
Posts: 28

Re: Merge

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 .
Super User
Posts: 9,682

Re: Merge

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;
Contributor afs
Contributor
Posts: 28

Re: Merge

can we complete the code without using proc sql?
Super User
Posts: 9,682

Re: Merge

[ Edited ]

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;

Ask a Question
Discussion stats
  • 6 replies
  • 249 views
  • 0 likes
  • 4 in conversation