SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Combination of sets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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.

 

Sans titre.png

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
Super User
Posts: 7,413

Re: Combination of sets

[ Edited ]

In light of the test data:

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.

View solution in original post


All Replies
Super User
Posts: 17,905

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 !

 

Respected Advisor
Posts: 3,777

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;

Capture.PNG

Solution
‎04-01-2016 09:33 AM
Super User
Super User
Posts: 7,413

Re: Combination of sets

[ Edited ]

In light of the test data:

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

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 ! 

Super User
Super User
Posts: 7,413

Re: Combination of sets

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.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 384 views
  • 2 likes
  • 4 in conversation