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

Hello,

 

I have two tables that i want to join. They have one column in commun which name is "Thême". In one table i get only one theme "TRANSFERT"' in the second I have 12 Theme.

I try to joint the tables in the aim to get one column "Theme" which contains the 13 items.

You can find the code of the 2 tables  "A faire theme Rennes " and "Stock Theme Rennes" and finaly the jointure i make not good...

 

By advance thank you a lot.

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_STOCKS_THEME_Rennes(label="Query") AS 
   SELECT t4.'Famille d''opération'n AS 'Thême'n, 
          /* COUNT_of_Famille d'opération */
            (COUNT(t4.'Famille d''opération'n)) AS 'COUNT_of_Famille d''opération'n
      FROM WORK.QUERY_FOR_STOCKS_POUR_PF_X t1, WORK.TABLE_LIBELL__CF1 t2, WORK.TABLE_DES_DSCS t3, WORK.TABLE_DES_TH_MES1 
          t4
      WHERE (t1.CFG = t2.CFG AND t2.'Libellé Centre Financier'n = t3.'Libellé Centre Financier'n AND t1.LB_TYP_OPE2 = t4.
           'Type d''opération'n) AND (t1.'Code statut physique'n = 'EE' AND t1.'Libellé famille opération'n NOT = 
           '5_RENSEIGNEMENT RCF' AND t3.DSCS = 'RENNES')
      GROUP BY t4.'Famille d''opération'n;
QUIT;


PROC SQL;
   CREATE TABLE WORK.QUERY_AFAIRE_THEME_RENNES(label="Query") AS 
   SELECT t3.'Famille d''opération'n AS 'Thême'n, 
          /* COUNT_of_Famille d'opération */
            (COUNT(t3.'Famille d''opération'n)) AS 'COUNT_of_Famille d''opération'n
      FROM WORK.QUERY_AFAIRE t1
           INNER JOIN WORK.TABLE_DES_TH_MES1 t3 ON (t1.LB_TYP_OPE2 = t3.'Type d''opération'n)
      WHERE t1.'Zone partition identifiant CRSF'n = 358100 
      GROUP BY t3.'Famille d''opération'n;
QUIT;


PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_QUERY_AFAIRE_THEME_REN(label="QUERY_FOR_QUERY_AFAIRE_THEME_RENNES") AS 
   SELECT t1.'Thême'n, 
          t2.'COUNT_of_Famille d''opération'n AS 'COUNT_of_Famille d''opération1'n, 
          t1.'COUNT_of_Famille d''opération'n
      FROM WORK.QUERY_AFAIRE_THEME_RENNES t1
           FULL JOIN WORK.QUERY_FOR_STOCKS_THEME_RENNES t2 ON (t1.'Thême'n = t2.'Thême'n);
QUIT;




 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

So you need to apply your logic to the example data I provided, and then post the code, so I can see where the difference is.

My code produces a dataset where proc print output is this:

Obs    make    sold    stock

 1     BMW       3       0  
 2     VW        2       0  
 3     Ford      4       0  
 4     KIA       0       6  

So you can see that the "make" column is appropriately filled.

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

First, get rid of those ridiculous names. They cause eye cancer and totally unnecessary typing, and make it next to impossible for non-french users to type for testing purposes.

Really.

I mean it.

 

Next, supply some test data ("have" and "want") to illustrate your intentions. Because of said eye-cancer, it is very hard to read that from the code.

Example data is best posted in the form of a data step with datalines.

 

Strong hint: use SAS column labels for descriptive column names.

peter2
Obsidian | Level 7

@Kurt_Bremser, sorry. I have saved the zip you talk about. But where is the autoexe?

 

To come back to the subject, imagine I have 2 tables with one column cars.

The first with BMW, Mercedes, Porsche.... in one column and numbers old sold in the other column

The second with only one value  : KIA in one column and the numbers of stock.

Which jointure do i have to do to put the kia with BMW in one colomn, the second colomn with the sold (so 0 for kia) ans a third column with stock (so 0 for BMW, Mercedes...)

Kurt_Bremser
Super User

Here's an example:

data cars1;
input make $ sold;
cards;
BMW 3
VW 2
Ford 4
;
run;

data cars2;
input make $ stock;
cards;
KIA 6
;
run;

data cars;
set
  cars1
  cars2
;
array _nums _numeric_;
do _i = 1 to dim(_nums);
  if _nums{_i} = . then _nums{_i} = 0;
end;
drop _i;
run;

The action with the array is solely to have a generic way to replace all missing numeric values with 0; if there are missing values present that need to be preserved, one must be more specific. Use the in= dataset option on the input datasets, and set the correct variables to 0 with the right condition (if not in_cars1 then stock = 0; )

peter2
Obsidian | Level 7

@Kurt_Bremser, thank you for trying to help me but i surely dont have well described what is the problem. When I do the jointure i have well "0" in the column. What I dont have is :

bmw

vw

mercedes

...

KIa

 

I only have

nothing               2                       0

nothing              4                       0

....                      6                       0 

kia                     0                       5

 

I want the name of other car instead of nothing...

   

 

 

Kurt_Bremser
Super User

So you need to apply your logic to the example data I provided, and then post the code, so I can see where the difference is.

My code produces a dataset where proc print output is this:

Obs    make    sold    stock

 1     BMW       3       0  
 2     VW        2       0  
 3     Ford      4       0  
 4     KIA       0       6  

So you can see that the "make" column is appropriately filled.

peter2
Obsidian | Level 7

@Kurt_Bremser, i have seen with a collegue...and i understand your data set, thanks a lot! It's OK

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
  • 6 replies
  • 883 views
  • 0 likes
  • 2 in conversation