Updating existing variable by another one.

[adil256]

Hi everyone,

I've a stupid question but I didn't find any topics about it. I just want to replace the value of a existing variable by another one through a merge. With Sas base, it's very easy to do but with Sas eg i don't know what to do. I tried to used "recoded variable" but it doesn't work.

 thx 🙂

[Kurt_Bremser]

Open a code window and enter the same code as in Base.

[adil256]

thx @Kurt_Bremser. I tried your method but it does not work for me. To be more explicite I merge 3 different tables based on different keys to get the information I want in Table 3. Then I try to replace the value of variable rate_ab in Table 1 by rate_ab1 from table 3.

Maybe should I use merge statement to get what I want. If it's not clear enough don't hesite to do some remarks.

Thank you 🙂

proc sql;
   
create table work.table_final as 

             select t1.id, 
                   t1.contrat, 
                   t1.statut,  
                   t1.product, 
                   t1.FamillyProduct, 
                   t1.sexe, 
                   t1.smoker, 
                   t1.waranty, 
                   t1.rate_ab,

            (t3.rate_ab1) AS rate_ab

from work .table1 t1 , work.table2 t2, work.table3 t3

where ( t1.product=t2.product                                  and 
             t2.group_tarification = t3.group_tarification and
             t1.sexe = t3.sexe                                         and
             t1.smoker = t3.smoker                                 and
             t1.warranty= t3.warranty );

quit;                             
 


      

[Kurt_Bremser]

If the code worked in base, (as you said in your initial post), then it will also work in an EG code window.

Are you now saying that you never had working code?

Please supply some example data (use data steps with datalines) to illustrate content and structure of your three tables, and what the expected result should look like.

[Tom]

You cannot have two variables with the same name.

Do you want two variables?  Perhaps you want to use the COALESCE() function to take the value from table3 when it is not missing and otherwise take the value from table1?

proc sql;
create table work.table_final as
   select t1.id
        , t1.contrat
        , t1.statut
        , t1.product
        , t1.FamillyProduct
        , t1.sexe
        , t1.smoker
        , t1.waranty
        , coalesce(t3.rate_ab1,t1.rate_ab) as rate_ab
        , t1.rate_ab as rate_ab_from1
        , t3.rate_ab1 as rate_ab_from3
  from work.table1 t1
     , work.table2 t2
     , work.table3 t3
  where t1.product=t2.product
    and t2.group_tarification = t3.group_tarification
    and t1.sexe = t3.sexe
    and t1.smoker = t3.smoker
    and t1.warranty= t3.warranty
;
quit;

[adil256]

I hope with tablesview, it will be more clear :).

Here my first table, I want to update the variable rate_ab without ceate a new variable based on information containing in table 3.

                                  Table1

Product	Sexe	warranty	Smoker	rate_ab
1	1	0	1	0
2	0	1	1	0
3	0	1	1	0
3	0	1	1	0
3	1	0	1	0
4	1	0	0	0
4	0	0	0	0
1	0	0	1	0
2	1	0	1	0
1	1	1	1	0

               Table 2

Product	GroupTarification
1	A2
2	A1
3	A1
4	A2
5	A2
6	A2
7	A1
8	A1
9	A2
10	A1

                                          Table3

GroupTarification	Sexe	warranty	Smoker	rate_ab
A1	0	0	0	0.25
A1	1	0	0	0.1
A1	0	1	0	0.35
A1	0	0	1	1
A1	1	1	0	1.25
A1	0	1	1	0.75
A1	1	0	1	0.15
A1	1	1	1	-0.35
A2	0	0	0	-0.5
A2	1	0	0	0.25
A2	0	1	0	0.95
A2	0	0	1	-0.25
A2	1	1	0	0.45
A2	0	1	1	0.85
A2	1	0	1	0.15
A2	1	1	1	0

Here what the final result shoud like.

                             Table_Final

Product	Sexe	warranty	Smoker	rate_ab
1	1	0	1	0.15
2	0	1	1	0.75
3	0	1	1	0.75
3	0	1	1	0.75
3	1	0	1	0.15
4	1	0	0	0.25
4	0	0	0	-0.5
1	0	0	1	-0.25
2	1	0	1	0.15
1	1	1	1	0

[Kurt_Bremser]

Please supply example data in a data step, as already mentioned. I won't type that off the screen.

[adil256]

My bad, still a noob sorry. hope it's ok :).

data Table1;
input Product Sexe warranty Smoker rate_ab;
infile datalines missover;
datalines;
1 1 0 1 0 
2 0 1 1 0 
3 0 1 1 0 
3 0 1 1 0 
3 1 0 1 0 
4 1 0 0 0 
4 0 0 0 0 
1 0 0 1 0 
2 1 0 1 0 
1 1 1 1 0 
;
data Table2;
input Product Groupe_tarification $;
infile datalines missover;
datalines;
1 A2 
2 A1 
3 A1 
4 A2 
5 A2 
6 A2 
7 A1 
8 A1 
9 A2 
10 A1 
;
data Table3;
input Groupe_tarification $ Sexe warranty Smoker rate_ab;
infile datalines missover;
datalines;
A1 0 0 0 0.25 
A1 1 0 0 0.1 
A1 0 1 0 0.35 
A1 0 0 1 1 
A1 1 1 0 1.25 
A1 0 1 1 0.75 
A1 1 0 1 0.15 
A1 1 1 1 -0.35 
A2 0 0 0 -0.5 
A2 1 0 0 0.25 
A2 0 1 0 0.95 
A2 0 0 1 -0.25 
A2 1 1 0 0.45 
A2 0 1 1 0.85 
A2 1 0 1 0.15 
A2 1 1 1 0 
;
data Table_Final;
input Product Sexe warranty Smoker rate_ab;
infile datalines missover;
datalines;
1 1 0 1 0.15 
2 0 1 1 0.75 
3 0 1 1 0.75 
3 0 1 1 0.75 
3 1 0 1 0.15 
4 1 0 0 0.25 
4 0 0 0 -0.5 
1 0 0 1 -0.25 
2 1 0 1 0.15 
1 1 1 1 0 
 ;
run;

[Kurt_Bremser]

Works like a breeze. Am now playing around with it.

[Kurt_Bremser · Accepted solution]

My method would be to create lookup formats:

data cntlin (keep=fmtname type start label);
set table2;
fmtname = 'product';
start = put(Product,2.);
label = Groupe_tarification;
type = 'N';
run;

proc format library=work cntlin=cntlin;
run;

data cntlin (keep=start fmtname type label);
set table3;
start = strip(Groupe_tarification) !! put(Sexe,1.) !! put(warranty,1.) !! put(Smoker,1.);
label = put(rate_ab,best.);
type = 'C';
fmtname = 'tariff';
run;

proc format library=work cntlin=cntlin;
run;

data want;
set table1;
tarcode = strip(put(product,product.)) !! put(Sexe,1.) !! put(warranty,1.) !! put(Smoker,1.);
rate = input(put(tarcode,$tariff.),best.);
run;

To facilitate that, I create a combined value from all rate-relevant columns. You may have to adapt this to your real data structures (eg non-numeric data or more digits).

BTW if you have boolean values, it saves space if you don't store them as numbers (minimum length 3), but as single characters.

Edit:

PS This is, of course, an application of Maxim 21.

[adil256]

Thank you very very much @Kurt_Bremser. The code is so smooth :-). I adapted to my data and it workds perfectly. I've another question related to this code. Let's imagine I need the information of two variables in Table3 how can i retrieve the value without rewrite the code for another variable.

I would have never thought about a code like that. It helps me to develop my skills. If you've any books to recommend, I'll be eager to read it.

thank u again

data Table1;
input Product Sexe warranty Smoker rate_ab ;
infile datalines missover;
datalines;
1 1 0 1 0 
2 0 1 1 0 
3 0 1 1 0 
3 0 1 1 0 
3 1 0 1 0 
4 1 0 0 0 
4 0 0 0 0 
1 0 0 1 0 
2 1 0 1 0 
1 1 1 1 0 
;
data Table2;
input Product Groupe_tarification $;
infile datalines missover;
datalines;
1 A2 
2 A1 
3 A1 
4 A2 
5 A2 
6 A2 
7 A1 
8 A1 
9 A2 
10 A1 
;
data Table3;
input Groupe_tarification $ Sexe warranty Smoker rate_ab id;
infile datalines missover;
datalines;
A1 0 0 0 0.25 AAA+
A1 1 0 0 0.1   AA+
A1 0 1 0 0.35  A+
A1 0 0 1 1  B
A1 1 1 0 1.25 BB 
A1 0 1 1 0.75 BB+
A1 1 0 1 0.15 AA
A1 1 1 1 -0.35 BBB
A2 0 0 0 -0.5 BBB+
A2 1 0 0 0.25 B++
A2 0 1 0 0.95 AA
A2 0 0 1 -0.25 BB
A2 1 1 0 0.45 AAA+
A2 0 1 1 0.85 C
A2 1 0 1 0.15 CC
A2 1 1 1 0 CCC+
;
data Table_Final;
input Product Sexe warranty Smoker rate_ab id;
infile datalines missover;
datalines;
1 1 0 1 0.15 
2 0 1 1 0.75
3 0 1 1 0.75 
3 0 1 1 0.75 
3 1 0 1 0.15 
4 1 0 0 0.25 
4 0 0 0 -0.5 
1 0 0 1 -0.25 
2 1 0 1 0.15 
1 1 1 1 0 
 ;
run;

[Kurt_Bremser]

As you can see, the application of formats to such kinds of tasks is represented in one of my Maxims. These Maxims are the result of 2 decades of working with SAS, and some more of working in the computer industry as such.

Aside from the documentation, I never really read a "SAS book". My "skills" are simply the result of knowing where to look, and "wasting" time in places like this community here. And not being afraid of navigating through the documentation; RTFM is still the best advice I can give (that's why it is Maxim 1).

And anytime the doc reads like Klingon to you, come here for a translation 😉

[Kurt_Bremser]

---

@adil256 wrote:

Thank you very very much @Kurt_Bremser. The code is so smooth :-). I adapted to my data and it workds perfectly. I've another question related to this code. Let's imagine I need the information of two variables in Table3 how can i retrieve the value without rewrite the code for another variable.

---

You just need to create an additional format that translates from the combined value to the id, and use that in the final step in an additional put() function. The four variables (fmtname, start, label, type) are the bare minimum you need to specify in a control dataset for proc format.