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

You didn't include the  "&location.\Codes\02_ Macro - Prediction.sas" ode, so I can t be 100% sure, but it looks like you are compiling your macros to a permanent library using code similar to this: libname OPTMACRO "some location"; options mstored sasmstore=OPTMACRO; %macro MyMacro1 /stored; /*...more macro code...*/ %mend; %macro MyMacro2 /stored; /*...more macro code...*/ %mend; /* And after the macro definitions: */ libname OPTMACRO clear; You'll get the "ERROR: Unable to clear or re-assign the library OPTMACRO because it is still in use" message because SAS is still using the SASMACR catalog in the OPTMACRO library for compiled macro storage. Inserting a %sysmstoreclear macro statement just before trying to clear the OPTMACRO libref should do the trick - something like this: %sysmstoreclear; libname OPTMACRO clear; May the SAS be with you! Mark     ... View more

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

Your case expression has no logical expression. It should look something like this: proc sql; select distinct Make , CASE (/* Logical Expression */ Type ) WHEN 'SUV' THEN 'N' ELSE 'Y' END from sashelp.cars ; quit; or like this: proc sql; select distinct Make ,CASE WHEN (type='SUV') THEN 'N' ELSE 'Y' END from sashelp.cars; quit;  e   ... View more

Thanks for posting these helpful instructions! ... View more

Peter made an excellent suggestion about taking the free Programing 1 course. I'd also like to know a little more detail about what's causing your concern. Are you already used to programming in SAS, and it's just the difference in the programming interface? and by "base SAS" do you mean you are now using PC SAS? Or perhaps you now edit your SAS code in a text editor and submit from the command line? If you could pinpoint the issues a bit. I'll try to provide additional resources. ... View more

Does PROC HTTP work for you? Try this: filename _d2d temp; proc http url='http://blogs.sas.com/content/sastraining/files/2018/08/data2datastep.txt' out=_d2d method="get"; run; %include _d2d; %data2datastep(!HELP) ... View more

OK - I'm fresh out of Wile E. Coyote ideas at the moment. Looks like if we use FTP, we have to abandon the SAS University Edition crowd. And if we use HTTP, it won't work in certain work environments and may be disabled in places outside of the US.  ... View more

OK - this needs a solution that works worldwide. How about anonymous FTP? FTP shouldn't require any special encryption.  Would you try this for me and see if it works on your system? filename _prg temp; filename _d2d ftp 'data2datastep.txt' cd='/outgoing/jedi' user='anonymous' host='ftp.sas.com'; data _null_; infile _d2d; file _prg; input; put _infile_; run; %include _prg; filename _d2d; filename _prg; %data2datastep(!HELP) ... View more

I tested this using a RAW URL (from my personal GITHUB project) and it took almost 2.5 minutes to execute vs. 1.9 seconds when served from a traditional web service. I'm serving it right now from blogs.sas.com (I have access to post there). We can probably find a way of uploading the file right here at communities.sas.com - that would probably be best.  I'll check around.. ... View more

I am not a fan of keyword parameters because the end user has to remember the macro variable names and type them correctly for every use. I prefer the simplicity of positional parameters combined with default values (provided in the macro code) and easy-to-use self documentation. It's probably more a matter of style and personal predilection.  I'm not a great 'rememberer', neither am I the world's best typist, so I have always personally found keyword parameters problematic.  ... View more

Wow - you guys are REALLY thinking about how to make this easy for a new user!  Thanks for all the thought put into this. I'm working on a new post with a slightly modified version of the macro to make it easier to use right out of the box. I've stolen prolifically from the suggestions here.  The idea of hosting the program file on http so that it was directly accessible was brilliant. I have a prototype up right now for testing.  Feel free to test and comment, but please don't formalize this - the URL will probably change when this goes live. Here's the code a new user would run to get set up: filename _d2d url 'http://blogs.sas.com/content/sastraining/files/2018/08/data2datastep.txt' debug; filename _prg temp; data _null_; infile _d2d; file _prg; input; put _infile_; run; %include _prg; filename _d2d; filename _prg; %data2datastep(!HELP) This produces the docs in the SAS log, but I've moved the "getting started quickly" help to the top, and the detailed syntax help to the bottom like this: NOTE: DATA2DATASTEP macro help document: Purpose: Writes a SAS DATA step program to re-create a SAS data set. Example - To create a SAS program to reproduce 10 rows of the dataset SASHELP.CARS, writing the copy to the SAS WORK library (WORK.CARS), just run this code: %DATA2DATASTEP(CARS,SASHELP,WORK,10) NOTE: Use %DATA2DATASTEP(!HELP) to print this detailed help for this macro. Syntax: %DATA2DATASTEP(dsn<,lib,outlib,obs,file,fmt,lbl>) dsn: Name of the dataset to be converted. Required. lib: LIBREF of the original dataset. (Optional - if DSN is not fully qualified) outlib: (Optional) LIBREF for the output dataset. Default is WORK obs: (Optional) Max observations to include the created dataset. Default is MAX (all observations) file: (Optional) Fully qualified filename for the DATA step code produced. Default is create_&outlib._&dsn._data.sas in the SAS default directory: S:\Workshop fmt: (Optional) Format the numeric variables like the original data set? (YES|NO) Default is YES lbl: (Optional) Reproduce column labels in the output dataset? (YES|NO) Default is YES Another minor mod is that, when finished creating code, the macro now reports where to find the program it created. For example, running this code: %DATA2DATASTEP(CARS,SASHELP,WORK,10) produces this response in the SAS log: ***************************************************************** Your SAS program which creates WORK.CARS is ready at S:\Workshop/create_WORK_CARS_data.sas *****************************************************************   I've tested this and it works in SAS University Edition, PC SAS and in SAS Enterprise Guide.  I'm eager to hear your thoughts and comments on how to make this better and simpler for the new user. ... View more