BookmarkSubscribeRSS Feed
Amin
Calcite | Level 5

Hi All

is it possible in SAS that sum two variables from different table and put it as a new variable in  a new table based on another variable as a key. for example like this :

table A :

IDAB
13321
24422
35533

Table B :

IDAB
14898
25512
36734

and in table c , sum A values and B values for same ID

Table C :

IDAB
181119
29934
312267
5 REPLIES 5
TarunKumar
Pyrite | Level 9

use below attcahed code;

data tablea;

input ID:$1. num_A num_B;

datalines;

1 33 21

2 44 22

3 55 33

;

run;

data tableB;

input ID:$1. num_A num_B;

datalines;

1 48 98

2 55 12

3 67 34

;

run;

proc sql;

create table table_new as

select a.id,a.num_a+b.num_a As Num_a,

a.num_b+b.num_b As Num_b

from tablea a

left join tableB b on a.id=b.id;

quit;

octrout
Calcite | Level 5
Hi, Can this be done in data step ?
mkeintz
PROC Star

Yes.  Since each table is sorted by ID, you can read them both, interleaving observations by ID.  Then just have an array of totals to update with each observations, and with the last obs for each ID, output those totals, as per the DATA WANT step below:

 

data tablea;
  input ID:$1. num_A num_B;
datalines;
1 33 21
2 44 22
3 55 33
run;

data tableB;
  input ID:$1. num_A num_B;
datalines;
1 48 98
2 55 12
3 67 34
run;

data want ;
  set tablea tableb;
  by id;
  array sums {2} _temporary_;
  array nums {2} num_a num_b;

  /* Starting an ID group?  Zero out the sums */
  if first.id then do I=1 to dim(sums);
    sums{I}=0;
  end;

  /* Increment the sums and copy results back to NUM_A and NUM_B */
  do I=1 to dim(sums);
    sums{i}+nums{I};
    nums{I}=sums{I};
  end;

  if last.id;   /* Subsetting IF */
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
octrout
Calcite | Level 5

How do I subtract instead of sums ? Thanks ! 

 

/* Increment the sums and copy results back to NUM_A and NUM_B */
  do I=1 to dim(sums);
    sums{i}+nums{I};
    nums{I}=sums{I};
  end;

 

mkeintz
PROC Star

What values are you subtacting from what other values?

 

Do you always have exactly two tables, and are those tables identical in their ID structure?  If so, assuming you want to substract table B from table A, you can still interleave the data sets using a SET with a BY statement, and then use the DIF function, as in:

 

 

data want;

  set tableb tablea;

  by id;

  array nums {2} num_a num_b;

  do I=1 to dim(sums);

    nums{I}=dif(nums{I});

  end;

  if last.id;

run;

 

 

The DIF function is  X-lag(X).  So when the record-in-hand is the second record for a by-group (i.e. the tablea record), it's subtracting the prior value (i.e. from tableB above) from the current (tableA).   Of course when the record-in-hand is the beginning of a by-group, the result is nonsense, but the subsetting "if last.id;" statement excludes that record.

 

 

If you had used "set tablea tableb" instead, you would have gotten B-A.

 

           

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 9332 views
  • 4 likes
  • 4 in conversation