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

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
Reeza
Super User

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

alisondu77
Obsidian | Level 7

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 !

 

data_null__
Jade | Level 19

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

alisondu77
Obsidian | Level 7

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 ! 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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