05-04-2017 03:59 PM
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;
input (id vch) ($) mt ;
civ a 30
sen b 20
sen a 10
civ c 30
buk c 15
input (id vch) ($) mt;
civ a 20
sen b 15
civ c 40
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:
I keep only one observation.
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 = 0 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 !
05-04-2017 04:38 PM
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;
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.
05-04-2017 06:03 PM
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 !
05-04-2017 05:37 PM
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
05-04-2017 06:27 PM
Sorry, I did not see your answer before making my first answer.
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.
05-04-2017 06:27 PM
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;
05-04-2017 06:44 PM
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 ChrisNZ.
Thank you so much to everyone, hope that i will learn more practicing
and developing my sas skills by your side.
Need further help from the community? Please ask a new question.