## Combination of sets

Solved
Occasional Contributor
Posts: 15

# Combination of sets

Hello everyone,

I have a solution to my problem, but I would like something more efficient and quicker.

I think you can understand on the picture what I want.

I would like to associate two sets (blue and green) or more, to do the red one (it's simply to combination of the 2 sets with null data when there is no number).

So this is my script SAS :

``````/* creation of the set where i save the result */
proc sql ;
create table lib.montant_result as (
select id_dossier, 0 as montant_1, 0 as montant_2,0 as montant_3, 0 as montant_4,
0 as montant_5, 0 as montant_6,0 as montant_7, 0 as montant_8, 0 as montant_9
from lib.clients ;
quit ;

%Macro evolution_montant(YYMM) ;
/* creation of the blue and green sets */
proc sql ;
create table lib.montant_month as (
select id_dossier,
case when intck("month",date,"01jul2013") = 1 then montant end as montant_1,
case when intck("month",date,"01jul2013") = 2 then montant end as montant_2,
case when intck("month",date,"01jul2013") = 3 then montant end as montant_3,
case when intck("month",date,"01jul2013") = 4 then montant end as montant_4,
case when intck("month",date,"01jul2013") = 5 then montant end as montant_5,
case when intck("month",date,"01jul2013") = 6 then montant end as montant_6,
case when intck("month",date,"01jul2013") = 7 then montant end as montant_7,
case when intck("month",date,"01jul2013") = 8 then montant end as montant_8,
case when intck("month",date,"01jul2013") = 9 then montant end as montant_9,
FROM lib.clients clients
inner join lib.montant&YYMM. mont on mont.id_dossier = clients.id_dossier );
quit ;

/*creation and update of the red set (result) */
proc sql ;
UPDATE lib.montant_result  a
SET montant_1 = sum(a.montant_1 ,(SELECT b.montant_1 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )),
montant_2 = sum(a.montant_2 ,(SELECT b.montant_2 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )) ,
montant_3 = sum(a.montant_3 ,(SELECT b.montant_3 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )) ,
montant_4 = sum(a.montant_4 ,(SELECT b.montant_4 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )),
montant_5 = sum(a.montant_5 ,(SELECT b.montant_5 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )),
montant_6 = sum(a.montant_6 ,(SELECT b.montant_6 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )),
montant_7 = sum(a.montant_7 ,(SELECT b.montant_7 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )),
montant_8 = sum(a.montant_8 ,(SELECT b.montant_8 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier )),
montant_9 = sum(a.montant_9 ,(SELECT b.montant_9 FROM  alison.montant_month  b WHERE a.id_dossier = b.id_dossier ))
;
quit ;

%mend  evolution_montant;``````

Thank you in advance. And sorry for my poor english !

Have a good weekend !

Accepted Solutions
Solution
‎04-01-2016 09:33 AM
Super User
Posts: 9,427

## Re: Combination of sets

[ Edited ]

In light of the test data&colon;

```data montant1206;
format id \$3. mt_1 9.2 mt_2 9.2 mt_3 9.2 mt_4  9.2 mt_5 9.2 mt_6  9.2 mt7 9.2 mt_8 9.2 mt_9 9.2;
input id mt_1 mt_2 mt_3 mt_4 mt_5 mt_6 mt7 mt_8 mt_9;
cards;
a12 1 . . . . . . . .
a13 . 2 . . . . . . .
a14 . . . . 4 . . . .
a15 . . . . . . . . .
a16 3 . . . . . . . .
;
run;proc transpose data=montant1206 out=t1; by id; var mt:;run;
data montant1207;
format id \$3. mt_1 9.2 mt_2 9.2 mt_3 9.2 mt_4  9.2 mt_5 9.2 mt_6  9.2 mt7 9.2 mt_8 9.2 mt_9 9.2;
input id mt_1 mt_2 mt_3 mt_4 mt_5 mt_6 mt7 mt_8 mt_9;
cards;
a12 . 2 . . . . . . .
a13 . . 3 . . . . . .
a14 . . . . . 6 . . .
a15 7 . . . . . . . .
a16 . 10 . . . . . . .
;
run;
proc transpose data=montant1207 out=t2;
by id;
var mt:;
run;

proc sql;
create table WANT as
select COALESCE(A.ID,B.ID) as ID,
COALESCE(A._NAME_,B._NAME_) as MONTH,
COALESCE(A.COL1,B.COL1) as RESULT
from   T1 A
full join T2 B
on     A.ID=B.ID
and    A._NAME_=B._NAME_;
quit;```

---

As @Reeza said.  The below is just a guess, but you can use the coalesce() function which takes the first non missing value.

```proc sql;
create table WANT as
select  COALESCE(A.ID_DOSSIER,B.ID_DOSSIER) as DOSSIER,
COALESCE(A.MONTANT_1,B.MONTANT_1) as MONTANT_1,
COALESCE(A.MONTANT_2,B.MONTANT_2) as MONTANT_1,
...
from    HAVE1 A
full join HAVE2 b
on       A.ID_DOSSIER=B.DOSSIER;
quit;```

But the question here is why not normalise your data, so you have something like:

ID_DOSSIER   MONTH   VALUE

1                     1             xyz

1                     2             xyz

...

It would then be simple merge statement, and you will find further processing on the data to be far easier than keeping the structure you currently have.

All Replies
Super User
Posts: 23,321

## Re: Combination of sets

We can't code off pictures though. Please include data as text.

Occasional Contributor
Posts: 15

## Re: Combination of sets

Oh sorry, it was just an exemple what i post here.

But i have translate the picture into code :

``````data lib.montant1206;
format id \$3. mt_1 9.2 mt_2 9.2 mt_3 9.2 mt_4  9.2 mt_5 9.2 mt_6  9.2 mt7 9.2 mt_8 9.2 mt_9 9.2;
input id mt_1 mt_2 mt_3 mt_4 mt_5 mt_6 mt7 mt_8 mt_9;
cards;
a12 1 . . . . . . . .
a13 . 2 . . . . . . .
a14 . . . . 4 . . . .
a15 . . . . . . . . .
a16 3 . . . . . . . .
;

data lib.montant1207;
format id \$3. mt_1 9.2 mt_2 9.2 mt_3 9.2 mt_4  9.2 mt_5 9.2 mt_6  9.2 mt7 9.2 mt_8 9.2 mt_9 9.2;
input id mt_1 mt_2 mt_3 mt_4 mt_5 mt_6 mt7 mt_8 mt_9;
cards;
a12 . 2 . . . . . . .
a13 . . 3 . . . . . .
a14 . . . . . 6 . . .
a15 7 . . . . . . . .
a16 . 10 . . . . . . .
;``````

Thank you !

Posts: 3,847

## Re: Combination of sets

UPDATE

``````data a;
format id \$3.;
input id mt_1-mt_9;
cards;
a12 1 . . . . . . . .
a13 . 2 . . . . . . .
a14 . . . . 4 . . . .
a15 . . . . . . . . .
a16 3 . . . . . . . .
;;;;
data b;
format id \$3.;
input id mt_1-mt_9;
cards;
a12 . 2 . . . . . . .
a13 . . 3 . . . . . .
a14 . . . . . 6 . . .
a15 7 . . . . . . . .
a16 . 10 . . . . . . .
;;;;
run;
data c;
update a b;
by id;
run;
Proc print;
run;``````

Solution
‎04-01-2016 09:33 AM
Super User
Posts: 9,427

## Re: Combination of sets

[ Edited ]

In light of the test data&colon;

```data montant1206;
format id \$3. mt_1 9.2 mt_2 9.2 mt_3 9.2 mt_4  9.2 mt_5 9.2 mt_6  9.2 mt7 9.2 mt_8 9.2 mt_9 9.2;
input id mt_1 mt_2 mt_3 mt_4 mt_5 mt_6 mt7 mt_8 mt_9;
cards;
a12 1 . . . . . . . .
a13 . 2 . . . . . . .
a14 . . . . 4 . . . .
a15 . . . . . . . . .
a16 3 . . . . . . . .
;
run;proc transpose data=montant1206 out=t1; by id; var mt:;run;
data montant1207;
format id \$3. mt_1 9.2 mt_2 9.2 mt_3 9.2 mt_4  9.2 mt_5 9.2 mt_6  9.2 mt7 9.2 mt_8 9.2 mt_9 9.2;
input id mt_1 mt_2 mt_3 mt_4 mt_5 mt_6 mt7 mt_8 mt_9;
cards;
a12 . 2 . . . . . . .
a13 . . 3 . . . . . .
a14 . . . . . 6 . . .
a15 7 . . . . . . . .
a16 . 10 . . . . . . .
;
run;
proc transpose data=montant1207 out=t2;
by id;
var mt:;
run;

proc sql;
create table WANT as
select COALESCE(A.ID,B.ID) as ID,
COALESCE(A._NAME_,B._NAME_) as MONTH,
COALESCE(A.COL1,B.COL1) as RESULT
from   T1 A
full join T2 B
on     A.ID=B.ID
and    A._NAME_=B._NAME_;
quit;```

---

As @Reeza said.  The below is just a guess, but you can use the coalesce() function which takes the first non missing value.

```proc sql;
create table WANT as
select  COALESCE(A.ID_DOSSIER,B.ID_DOSSIER) as DOSSIER,
COALESCE(A.MONTANT_1,B.MONTANT_1) as MONTANT_1,
COALESCE(A.MONTANT_2,B.MONTANT_2) as MONTANT_1,
...
from    HAVE1 A
full join HAVE2 b
on       A.ID_DOSSIER=B.DOSSIER;
quit;```

But the question here is why not normalise your data, so you have something like:

ID_DOSSIER   MONTH   VALUE

1                     1             xyz

1                     2             xyz

...

It would then be simple merge statement, and you will find further processing on the data to be far easier than keeping the structure you currently have.

Occasional Contributor
Posts: 15

## Re: Combination of sets

That's exactly what I want ! I didn't know the function "COALESC" !

And to reply to your question, I agree with you but it's not my data and I can not change the dataset. Sets are really to big.

Thank you again !

Super User
Posts: 9,427