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;
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.
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.
@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...)
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; )
@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...
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.
@Kurt_Bremser, i have seen with a collegue...and i understand your data set, thanks a lot! It's OK
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 25. 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.