Desktop productivity for business analysts and programmers

Problem on a juncture

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Problem on a juncture

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;




 

 


Accepted Solutions
Solution
‎11-10-2016 10:47 AM
Super User
Posts: 7,386

Re: Problem on a juncture

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.

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

View solution in original post


All Replies
Super User
Posts: 7,386

Re: Problem on a juncture

[ Edited ]

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.

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

Re: Problem on a juncture

@KurtBremser, 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...)

Super User
Posts: 7,386

Re: Problem on a juncture

[ Edited ]

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

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

Re: Problem on a juncture

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

   

 

 

Solution
‎11-10-2016 10:47 AM
Super User
Posts: 7,386

Re: Problem on a juncture

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.

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

Re: Problem on a juncture

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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 237 views
  • 0 likes
  • 2 in conversation