turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Updating existing variable by another one.

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2017 08:21 AM

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

Accepted Solutions

Solution

05-11-2017
10:07 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 05:37 AM - edited 05-11-2017 05:43 AM

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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-09-2017 08:36 AM

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

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

05-10-2017 10:51 AM

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

```
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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-10-2017 11:36 AM

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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-10-2017 11:42 AM - edited 05-10-2017 11:42 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 04:15 AM - edited 05-11-2017 04:20 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 04:27 AM

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

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

05-11-2017 04:58 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 05:06 AM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Solution

05-11-2017
10:07 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 05:37 AM - edited 05-11-2017 05:43 AM

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

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

05-11-2017 10:04 AM - edited 05-11-2017 10:14 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 10:17 AM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adil256

05-11-2017 10:24 AM

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

How to convert datasets to data steps

How to post code