topic Re: Combination of sets in SAS Data Management

Combination of sets

alisondu77 — Fri, 01 Apr 2016 13:04:13 GMT

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 !

Re: Combination of sets

Reeza — Fri, 01 Apr 2016 13:07:36 GMT

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

Re: Combination of sets

alisondu77 — Fri, 01 Apr 2016 13:24:21 GMT

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 !

Re: Combination of sets

RW9 — Fri, 01 Apr 2016 13:34:22 GMT

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.

Re: Combination of sets

data_null__ — Fri, 01 Apr 2016 13:32:14 GMT

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;

Re: Combination of sets

alisondu77 — Fri, 01 Apr 2016 13:36:56 GMT

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

Thank you so much for your quick and efficient answer !

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 !

Re: Combination of sets

RW9 — Fri, 01 Apr 2016 13:40:16 GMT

You can always change the data You work on, its is irrelevant so long as what goes out is what is required - how you process it is up to you. Anyways, glad you like the coalesce function. I would suggest you alos look at @data_null__s suggestion also, especially if you have lots of data and want to keep the structure. Personally I would go normalised, and only transpose at the end.