topic Re: Problem on a juncture in SAS Enterprise Guide

Problem on a juncture

peter2 — Thu, 10 Nov 2016 11:48:05 GMT

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;

Re: Problem on a juncture

Kurt_Bremser — Thu, 10 Nov 2016 13:18:06 GMT

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.

Re: Problem on a juncture

peter2 — Thu, 10 Nov 2016 13:34:06 GMT

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

Re: Problem on a juncture

Kurt_Bremser — Thu, 10 Nov 2016 13:50:52 GMT

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

Re: Problem on a juncture

peter2 — Thu, 10 Nov 2016 14:32:26 GMT

@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

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

Re: Problem on a juncture

Kurt_Bremser — Thu, 10 Nov 2016 14:40:09 GMT

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.

Re: Problem on a juncture

peter2 — Thu, 10 Nov 2016 15:46:58 GMT

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