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 🙂
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.
Open a code window and enter the same code as in Base.
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;
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.
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;
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 |
Please supply example data in a data step, as already mentioned. I won't type that off the screen.
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;
Works like a breeze. Am now playing around with it.
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.
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;
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 😉
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: