So, the trick is to apply the $CHAR3. format to the TrimCptbl column when you create it. This will cause SAS to preserve the leading space then the value is presented to Excel during PROC EXPORT. For example:
PROC SQL;
CREATE TABLE WORK.BEN_CONSO_FAMILLE_02 AS
SELECT DISTINCT t1.AnneeSurv,
t1.College,
t1.Couverture,
t1.Garantie,
t1.TypeBenef,
t1.Famille,
/* AnneeCptbl */
(FLOOR(t1.MIN_of_AnneeTrimCptbl/10)) AS AnneeCptbl,
/* TrimCptbl */
(" T" || put( MOD(t1.MIN_of_AnneeTrimCptbl, 10) , 1.)) FORMAT=$CHAR3. AS TrimCptbl, /* NbBenefConso */
(COUNT(t1.'Id Beneficiaire'n)) AS NbBenefConso,
/* MntRC */
(SUM(t1.SUM_of_MntRC)) FORMAT=COMMAX8.2 AS MntRC,
/* NbActes */
(SUM(t1.SUM_of_NbActes)) FORMAT=BEST12. AS NbActes,
/* NbActesOptions */
(SUM(t1.SUM_of_NbActesOptions)) FORMAT=BEST12. AS NbActesOptions
FROM WORK.BEN_CONSO_FAMILLE_01 t1
WHERE t1.SUM_of_NbActes NOT = 0 OR t1.SUM_of_NbActesOptions NOT = 0
GROUP BY t1.AnneeSurv,
t1.College,
t1.Couverture,
t1.Garantie,
t1.TypeBenef,
t1.Famille,
(CALCULATED AnneeCptbl),
(CALCULATED TrimCptbl);
QUIT;
proc export
data=WORK.BEN_CONSO_FAMILLE_02
outfile= "&RepertoireGlobal.\&Offre.\1. Données source\&AnneeMois.\4_4_BENEFICIAIRES_CONSOMMANT"
DBMS=xlsx REPLACE;
SHEET="Source_Prest_Benef_Cons_Famille";
run;
May the SAS be with you!
Mark
... View more
