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

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

12 REPLIES 12
adil256
Quartz | Level 8

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
Super User

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
Super User Tom
Super User

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
Quartz | Level 8

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

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
adil256
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Super User

@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.

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!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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