data management

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

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
Esteemed Advisor
Posts: 7,280

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

 

View solution in original post


All Replies
Grand Advisor
Posts: 10,192

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: 39

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   

 

Attachment
Esteemed Advisor
Posts: 7,280

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

 

Esteemed Advisor
Posts: 7,280

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: 39

Re: data management

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

Attachment
Solution
‎05-04-2017 06:37 PM
Esteemed Advisor
Posts: 7,280

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

 

Trusted Advisor
Posts: 1,506

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: 39

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 165 views
  • 0 likes
  • 4 in conversation