topic Re: Export to an excel file without trimming in New SAS User

Export to an excel file without trimming

othman — Tue, 05 Feb 2019 20:25:15 GMT

Hello everyone,

I'm trying to export 3 different tables into 3 sheet of the same excel file.

The new version of sas is trimming one column during the export;

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;

proc export data=WORK.BEN_CONSO_TOTAL_FAMILLE_02 outfile= "&RepertoireGlobal.\&Offre.\1. Données source\&AnneeMois.\4_4_BENEFICIAIRES_CONSOMMANT"

DBMS=xlsx REPLACE;

SHEET="Source_Prest_Benef_Cons_Total";

run;

proc export data=WORK.BEN_CONSO_PAR_AP_02 outfile= "&RepertoireGlobal.\&Offre.\1. Données source\&AnneeMois.\4_4_BENEFICIAIRES_CONSOMMANT"

DBMS=xlsx REPLACE;

SHEET="Source_Prest_Benef_Cons_AP";

run;

thank you in advance for your help

Re: Export to an excel file without trimming

SASJedi — Tue, 05 Feb 2019 20:38:39 GMT

In order for us to be able to help, I think we need a bit more information. It would be nice to have a few observations of each of the data sets being exported to test with, and an example of what you mean when you say "sas is trimming one column during the export". Does that mean one column is left out of the Excel tab, that the trailing blanks in text columns are not in the Excel file or perhaps one or more of the columns is being truncated? A small copy of the offending Excel file (or a least a screen shot showing the problem) would help get a more rapid response .

You can DATA step program to create the sample data for you using the %data2datastep macro - see the article "How to create a data step version of your data AKA generate sample data for forums"

All the best,

Mark

Re: Export to an excel file without trimming

Reeza — Tue, 05 Feb 2019 21:08:03 GMT

PROC EXPORT doesn't 'trim' anything during the export process, it shouldn't change the input, except perhaps to strip formats.

Can you explain what you mean by TRIMMING? Have you verified your source data is as expected using PROC PRINT?

@othman wrote:

Hello everyone,

I'm trying to export 3 different tables into 3 sheet of the same excel file.

The new version of sas is trimming one column during the export;

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;

proc export data=WORK.BEN_CONSO_TOTAL_FAMILLE_02 outfile= "&RepertoireGlobal.\&Offre.\1. Données source\&AnneeMois.\4_4_BENEFICIAIRES_CONSOMMANT"

DBMS=xlsx REPLACE;

SHEET="Source_Prest_Benef_Cons_Total";

run;

proc export data=WORK.BEN_CONSO_PAR_AP_02 outfile= "&RepertoireGlobal.\&Offre.\1. Données source\&AnneeMois.\4_4_BENEFICIAIRES_CONSOMMANT"

DBMS=xlsx REPLACE;

SHEET="Source_Prest_Benef_Cons_AP";

run;

thank you in advance for your help

Re: Export to an excel file without trimming

othman — Wed, 06 Feb 2019 08:11:18 GMT

Yes of course,

you will find attached a sample including :

The Data imported
The data seen in the sas GUI and expected.
The data after export.

Basically the program import the data and run som procedure to calculte the amount displayed.

The matter is within the "TrimCptbl" column that i have created in SAS. However After using the proc export or a print export the blank is erased.

Thank you in Advance

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

Re: Export to an excel file without trimming

Reeza — Wed, 06 Feb 2019 15:53:22 GMT

Looks like it's a leading space you want to keep. Try ODS EXCEL, with PROC REPORT to generate the data and a $CHAR format instead applied to the column of interest.You could also try applying a CHAR format first and exporting, but I suspect Excel will remove that as well.

Re: Export to an excel file without trimming

Tom — Wed, 06 Feb 2019 18:22:35 GMT

One way is to use ASIS=ON style element with ODS output.

Let's make some sample data with leading spaces.

data test;
  input a b $ c ;
  if a then b=repeat(' ',a-1)||b ;
  format b $char8.;
cards;
0 T 1
1 T 2
2 T 3
;

Now if we just print it with the $CHAR format attached the values look fine in the listing output.

Obs    a    b         c

 1     0    T         1
 2     1     T        2
 3     2      T       3

But not in the "pretty" ODS outputs.

If we add style option

ods excel file="&path\test3.xlsx" ;
proc report data=test;
 column _all_;
 define a / display;
 define b / display style(column)=[asis=on]  ;
 define c / display;
run;
ods excel close;

then leading spaces will appear in HTML and EXCEL destinations.

Re: Export to an excel file without trimming

SASJedi — Mon, 11 Feb 2019 12:29:27 GMT

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