topic Re: data management in SAS Programming

data management

DoumbiaS — Thu, 04 May 2017 19:59:18 GMT

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 = 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 !

Re: data management

ballardw — Thu, 04 May 2017 20:38:41 GMT

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.

Re: data management

art297 — Thu, 04 May 2017 21:37:53 GMT

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

Re: data management

DoumbiaS — Thu, 04 May 2017 22:03:58 GMT

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

Re: data management

art297 — Thu, 04 May 2017 22:12:52 GMT

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

Re: data management

DoumbiaS — Thu, 04 May 2017 22:27:11 GMT

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.

Thank you.

Regards

Re: data management

ChrisNZ — Thu, 04 May 2017 22:27:48 GMT

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

Re: data management

art297 — Thu, 04 May 2017 22:32:59 GMT

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

Art, CEO, AnalystFinder.com

Re: data management

DoumbiaS — Thu, 04 May 2017 22:44:34 GMT

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.

Regards