BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DoumbiaS
Quartz | Level 8

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
ballardw
Super User

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.

 

DoumbiaS
Quartz | Level 8

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   

 

art297
Opal | Level 21

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

 

art297
Opal | Level 21

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

 

DoumbiaS
Quartz | Level 8

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


your_want.PNG
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

ChrisNZ
Tourmaline | Level 20

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
DoumbiaS
Quartz | Level 8

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

 

 

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
  • 8 replies
  • 678 views
  • 0 likes
  • 4 in conversation