Solved
Contributor
Posts: 67

# data management

Hey ! Someone here could help me please ! I have 2 datasets like below.

Table1 has more variables than table2, both have commun variables.

But i'm focusting on id, vch and mt commun variables;

data table1;

input (id vch) (\$)  mt ;

cards;

civ     a     30

sen   b     20

sen   a     10

civ     c     30

buk   c     15

;run;

data table2;

input  (id vch) (\$)  mt;

cards;

civ    a   20

sen   b  15

civ    c   40

;run;

This is the resulting table i'm seeking for. And i try explainations below.

civ     a     10              [= 30-20]

sen    b     05             [= 20-15]

sen    a    10              [because there is no combination "sen   a"  in table2]

civ     c     00              [because 40>30]

buk    c     05              [= 15-(40-30)]

I'm concatening the 2 tables.

For an observation for which this 2 variables (id, vch) are the same:

First:

I keep only one observation.

Second:

For this only and remaining obs,

if  table1.var3 >= table2.var3   then  var3 =  sum(table1.var3, - table2.var3)

if  table1.var3 < table2.var3     then  var3 = and sum(- table1.var3, table2.var3)

will be allocated to another obs which as the same vch value. In this case, this other obs always exist.

Thanks a lot !

Accepted Solutions
Solution
‎05-04-2017 06:37 PM
PROC Star
Posts: 8,164

## Re: data management

Other than the order of the records, my result and your original desired table are (I think) identical!

Art, CEO, AnalystFinder.com

All Replies
Super User
Posts: 13,524

## Re: data management

A data step and merge solution for the first part:

```proc sort data=table1;
by id vch;
run;
proc sort data=table2;
by id vch;
run;

data want;
merge
table1
table2 (rename=(mt = mt2));
by id vch;
if missing(mt2) then newmt = mt;
else if mt2 > mt then newmt=0;
else newmt= mt - mt2;
run;```

sql

```proc sql;
create table want as
select a.id, a.vch,
case
when missing (b.mt) then 0
when  b.mt > a.mt then a.mt
else a.mt - b.mt
end as newmt
from table1 as a left join table2 as b
on a.id=b.id and a.vch=b.vch;
quit;```

I have to say that you will need to manually supply an example of your output with this var3 stuff as 1) you don't have var2 in either table1 or table2 but reference it as existing and it isn't clear quite what you mean as sql sum does not work that way.

Contributor
Posts: 67

## Re: data management

Thank you so much for you precious help.

True absolutly, var3 does not exist. I mean by var3, the third variable in both tables, that is exactly mt.

I was not coding in my explanation, using this notation [table.var3], i tried to say [var3 of table1]. Sorry.

I enclose a true capture of table1, table2 and table_want like i mean.

Using the merge data step and the proc sql above, i haven't the result wanted. Of course because of me

in my first post.

Thank for you helping me !

Regards

PROC Star
Posts: 8,164

## Re: data management

Your new want table doesn't come close to your previous one. The code I suggested, earlier, matches what you then said you wanted.

Art, CEO, AnalystFinder.com

PROC Star
Posts: 8,164

## Re: data management

I think that the following does what you want:

```proc sql noprint;
create table need as
select a.id,a.vch,a.mt-b.mt as rem,
case
when b.mt is null then a.mt
else max(0,(a.mt-b.mt))
end as mt,
case
when b.mt is null then 1
when (a.mt-b.mt) lt 0 then 2
else 0
end as test
from table1 a full join table2 b
on a.id=b.id and
a.vch=b.vch
order by a.vch,test
;
quit;

data want (keep=id vch mt);
set need;
by vch;
if test eq 1 and not last.vch then do;
pointer=_n_+1;
set need (keep=rem test rename=(rem=rem2 test=test2)) point=pointer;
if test2=2 then mt=mt+rem2;
end;
run;
```

Art, CEO, AnalystFinder.com

Contributor
Posts: 67

## Re: data management

I enclose the want result table i find with your code. It is different from mine.

Thank you for your help, i will continue helping myself with the code you all give me.

Thank you.

Regards

Solution
‎05-04-2017 06:37 PM
PROC Star
Posts: 8,164

## Re: data management

Other than the order of the records, my result and your original desired table are (I think) identical!

Art, CEO, AnalystFinder.com

PROC Star
Posts: 2,350

## Re: data management

Like this?

``````data TABLE1(index=(A=(ID VCH)));
input (ID VCH) (\$)  MT ;
cards;
civ     a     30
sen   b     20
sen   a     10
civ     c     30
buk   c     15
run;
data TABLE2(index=(A=(ID VCH)));
input  (ID VCH) (\$)  MT;
cards;
civ    a   20
sen   b  15
civ    c   40
run;

data TMP(index=(A=(VCH SUM)));
merge TABLE1 TABLE2(rename=(MT=MT2));
by ID VCH;
SUM=sum(MT,-MT2);
run;

data WANT;
retain ALLOC;
set TMP;
by VCH SUM;
if ALLOC then do;
SUM=SUM+ALLOC;
ALLOC=0;
end;
if SUM<0 then do;
ALLOC=SUM;
SUM=0;
end;
run;

``````

ID VCH SUM
civ a 10
sen a 10
sen b 5
civ c 0
buk c 5
Contributor
Posts: 67

## Re: data management

Oh great think to all, for your spontanous responses !

The first solution give by art297 is absolutly true and like i want.

Also as the one give by

☑ This topic is solved.