Desktop productivity for business analysts and programmers

Updating existing variable by another one.

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Updating existing variable by another one.

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 Smiley Happy


Accepted Solutions
Solution
‎05-11-2017 10:07 AM
Super User
Posts: 7,431

Re: Updating existing variable by another one.

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,431

Re: Updating existing variable by another one.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: Updating existing variable by another one.

thx @KurtBremser. 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 Smiley Happy

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;                             
 


      
Super User
Posts: 7,431

Re: Updating existing variable by another one.

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,845

Re: Updating existing variable by another one.

[ Edited ]

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;
Contributor
Posts: 30

Re: Updating existing variable by another one.

[ Edited ]

I hope with tablesview, it will be more clear Smiley Happy.

 

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

ProductSexewarrantySmokerrate_ab
11010
20110
30110
30110
31010
41000
40000
10010
21010
11110

 

               Table 2

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

 

                                          Table3

GroupTarificationSexewarrantySmokerrate_ab
A10000.25
A11000.1
A10100.35
A100

1

1
A1

1

101.25
A10110.75
A11010.15
A1111-0.35
A2000-0.5
A21000.25
A20100.95
A200

1

-0.25
A2

1

100.45
A20110.85
A21010.15
A21110

 

Here what the final result shoud like.

                             Table_Final

ProductSexewarrantySmokerrate_ab
11010.15
20110.75
30110.75
30110.75
31010.15
41000.25
4000-0.5
1001-0.25
21010.15
11110
Super User
Posts: 7,431

Re: Updating existing variable by another one.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: Updating existing variable by another one.

My bad, still a noob sorry. hope it's ok Smiley Happy.

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;

  

Super User
Posts: 7,431

Re: Updating existing variable by another one.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎05-11-2017 10:07 AM
Super User
Posts: 7,431

Re: Updating existing variable by another one.

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: Updating existing variable by another one.

[ Edited ]

Thank you very very much @KurtBremser. 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;
Super User
Posts: 7,431

Re: Updating existing variable by another one.

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 Smiley Wink

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,431

Re: Updating existing variable by another one.


adil256 wrote:

Thank you very very much @KurtBremser. 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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 166 views
  • 0 likes
  • 3 in conversation