optimize the name of export files

[PierreYvesILY]

dear SAS experts,

I use the following code to name datasets and afterwards export files. I encounter the following issue: the name end with a number of blanks (different according to the text of the name) between the name and the .xlsx for instance, like: NAME_variable   .xlsx

I need names to be:

- MONTH_YEAR (if the parameter Spanne is set at 1)

- FIRSTMONTH_ENDMONTH_YEAR  (if the parameter Spanne is set from 2 to 12).

Spanne = number of month reversely to a date (Auswertungsdatum) See below.

The code is highly under optimized (I'm quite new to SAS) and I guess there is a way to improve this a lot.

Can anyone help and send me advide to use a more clever structure of the code?

/* macro-code fuer den Auswertungstag */

%macro auswert;

%global Auswertungstag;

%let Auswertungstag=%sysfunc(today());

%mend auswert;

%auswert;

%put &Auswertungstag.;

%let Auswertungstag = '01JAN2021'D; /* Zur Nutzung: den Stern löschen und das ausgewählte Datum anpassen*/

%put &Auswertungstag.;

%let Spanne=12; /* Hier die Anzahl der gewünschten Monate rückwirkend ab den Vormonat erfassen */

%put &Spanne.;

%let Zeitraum_aus_ab = '01APR2019'D;

%let Zeitraum_aus_bis = '15APR2019'D;

%let NKF_EfDtm = '15SEP2019'D; /* NKFlag_Einfuehrungsdatum: 16 September 2019 */

%put &NKF_EfDtm.;

%put &Zeitraum_aus_ab.;

%put &Zeitraum_aus_bis.;

%macro Monat(Auswertungstag);

Monat=month(&Auswertungstag.)-1;

if Monat = 0 then Monat = 12;

%mend Monat;

%macro Jahr1(Auswertungstag);

/*Jahr1=(year(&Auswertungstag.)); */

Jahr1=(year(&Auswertungstag.)-1); /* nur für Dezember nutzen */

%mend Jahr1;

%macro Jahr2(Auswertungstag);

/* Jahr2=(year(&Auswertungstag.)-1); */

Jahr2=(year(&Auswertungstag.)-2); /* nur für Dezember nutzen */

%mend Jahr2;

　

%global Monat Jahr1 Jahr2 Monat1 Monat2 Jahr11 Jahr12 MonatBis MonatAb Spanne Dashboard_Name

Dashboard_Name1 Dashboard_Name2 Dashboard_Name3;

data zeitraum_Var;

Auswertungstag=&Auswertungstag.;

Spanne=&Spanne.;

format Auswertungstag DDMMYY10. Jahr1 best4. Jahr2 best4. Monat best2. MonatBis best2. MonatAb best2. Spanne best2. Dashboard_Name $31. Dashboard_Name1 $31. Dashboard_Name2 $31. Dashboard_Name3 $31.;

length Jahr11 $4 Jahr12 $4 Jahr1 4 Jahr2 4 Monat1 $9 Monat2 $9;

%Jahr1(&Auswertungstag.);

%Jahr2(&Auswertungstag.);

%Monat(&Auswertungstag.);

Jahr11=trim(substr(Jahr1,9,4));

Jahr12=trim(substr(Jahr2,9,4));

select (Monat);

when (1) Monat1 = 'Januar';

when (2) Monat1 = 'Februar';

when (3) Monat1 = 'März';

when (4) Monat1 = 'April';

when (5) Monat1 = 'Mai';

when (6) Monat1 = 'Juni';

when (7) Monat1 = 'Juli';

when (8) Monat1 = 'August';

when (9) Monat1 = 'September';

when (10) Monat1 = 'Oktober';

when (11) Monat1 = 'November';

when (12) Monat1 = 'Dezember';

otherwise;

end;

Monat2=trim(Monat1);

MonatBis=Monat;

MonatAb=Monat-&Spanne.+1;

select (MonatAb);

when (1) Monat3 = 'Januar';

when (2) Monat3 = 'Februar';

when (3) Monat3 = 'März';

when (4) Monat3 = 'April';

when (5) Monat3 = 'Mai';

when (6) Monat3 = 'Juni';

when (7) Monat3 = 'Juli';

when (8) Monat3 = 'August';

when (9) Monat3 = 'September';

when (10) Monat3 = 'Oktober';

when (11) Monat3 = 'November';

when (12) Monat3 = 'Dezember';

otherwise;

end;

Jahrdisplay = put(Jahr1,4.);

if Spanne = 1 then Dashboard_Name1 = trim(Monat2)||'_'||trim(Jahrdisplay);

else Dashboard_Name1 = trim(Monat3)||'_bis_'||trim(Monat2)||'_'||trim(Jahrdisplay);

Dashboard_Name=trim(Dashboard_Name1);

call symput('Monat', Monat);

call symput('Monat1', Monat1);

call symput('Monat2', Monat2);

call symput('Jahr1', Jahr1);

call symput('Jahr2', Jahr2);

call symput('Jahr11', Jahr11);

call symput('Jahr12', Jahr12);

call symput('MonatAb', MonatAb);

call symput('MonatBis', MonatBis);

call symput('Dashboard_Name', Dashboard_Name);

run;

/* zu Kontrolle in das LOG schreiben */

%put &Monat.;

%put &Monat1.;

%put &Monat2.;

%put &MonatAb.;

%put &MonatBis.;

%put &Jahr1.;

%put &Jahr2.;

%put &Jahr11.;

%put &Jahr12.;

%put &Dashboard_Name.;

[PaigeMiller]

I haven't really tried to follow your code all the way through, but you don't have any spot in your code where .xlsx appears. So where is the file name being created?

I also am not seeing any need for macros, as all of your calculations can be done in a DATA step.

[PierreYvesILY]

Hi Paige,

the files .xlsx are created at the bottom of the programs where I use the bloc of code I mentioned (let's name it: name_DIY bloc).

I may use some of the macrovariables of this bloc in the text of the program also:

&Auswertungstag.

&Spanne.

&Zeitraum_aus_ab.

&Zeitraum_aus_bis.

&Monat.;

&Monat1.;

&Monat2.;

&MonatAb.;

&MonatBis.;

&Jahr1.;

&Jahr2.;

&Jahr11.;

&Jahr12.;

I write the programs like that:

- the mentioned name_DIY bloc ; I'll use for instance the name: &Dashboard_Name.

- dataset generating code ; the dataset is, for instance: WORK.ORB

- export procedure:

/* Daten nach Windows übertragen */

PROC EXPORT DATA=ORB_Monat_6

OUTFILE= "&PRODUCT_BASEDIR./data/export/ORB_&Dashboard_Name..xlsx"

DBMS=XLSX REPLACE;

SHEET="ORB";

RUN;

/* Transfer nach Windows */

%mglob_secureTrans(MODE = PUT

,AUTHKEY = TRANS

,UNIXFILE = &PRODUCT_BASEDIR./data/export/ORB_&Dashboard_Name..xlsx

,CIFSFILE = FROM_UNIX\Reports\MUC\ORB_&Dashboard_Name..xlsx);

/* Löschen des BAK- und Excel-Files auf Unix */

data _null_;

fname="tempfile";

rc=filename(fname,"&PRODUCT_BASEDIR./data/export/ORB_&Dashboard_Name..xlsx");

if rc = 0 and fexist(fname) then rc=fdelete(fname);

rc=filename(fname);

run;

data _null_;

fname="tempfile";

rc=filename(fname,"&PRODUCT_BASEDIR./data/export/ORB_&Dashboard_Name..xlsx.bak");

if rc = 0 and fexist(fname) then rc=fdelete(fname);

rc=filename(fname);

run;

I hope it helps to understand my concern.

Thank you for your help,

PY

[PaigeMiller]

Use call symputx as @Reeza said

or

PROC EXPORT DATA=ORB_Monat_6 
OUTFILE= "&PRODUCT_BASEDIR./data/export/ORB_%trim(&Dashboard_Name).xlsx"
DBMS=XLSX REPLACE;

But really, you don't need macros here, your macros %JAHR1 %JAHR2 and %MONAT are unnecessary; data step commands work just fine on macro variable &Auswertungstag.

Also, its not clear to me why you have both numeric and character versions of JAHR1, seems unnecessary and redundant.

[PierreYvesILY]

The other macro variables are used in different program configurations, regarding the data source I have. Some define years as Numeric, others as Alphanumeric, and I need to fix the parameters for my selections somewhere. It's difficult to explain, but defining this standard bloc to set some fix group of macros helps me a lot in different programs. I cannot send the program texts, which you could then certainly optimize, but I'm moving forward step by step.

Regarding the name of the export itself, my problem is the following:

- I have 2 parameters: Auswertungstag (date) and Spanne (number of months, starting reversely to the month of Auswertungstag, but only the same year)

example1: Auswertungstag =01.03.2020 and Spanne = 1  then Name = Februar_2020

example2: Auswertungstag =01.03.2020 and Spanne = 2  then Name = Januar_bis_Februar_2020

example3: Auswertungstag =01.03.2020 and Spanne = 5  then Name = error

- names of the months are in german.

- the Name will be used to name export files without trailing blanks. ORB_Januar_bis_Februar_2020.xlsx and not ORB_Januar_bis_Februar_2020    .xlsx for instance.

Maybe it's a better way to explain the problem.

[PaigeMiller]

---

@PierreYvesILY wrote:

The other macro variables are used in different program configurations, regarding the data source I have. Some define years as Numeric, others as Alphanumeric, and I need to fix the parameters for my selections somewhere. It's difficult to explain, but defining this standard bloc to set some fix group of macros helps me a lot in different programs. I cannot send the program texts, which you could then certainly optimize, but I'm moving forward step by step.

---

Macros and macro variables are not the same thing. I see why you need macro variables. I said you don't need macros anywhere that I can see, especially the three in the data step.

[Reeza · Accepted solution]

Replace your CALL SYMPUT with CALL SYMPUTX()

SYMPUTX() removes any trailing spaces automatically and is what you want. It has some other options but they're not relevant to your question.

EDIT: And use CATT() to concatenate the components not || symbol. The CATT() will automatically trim extra spaces as well. If you're adding a delimiter between the terms, use CATX() instead.

[Reeza]

There may be functions to map the month number to month names. If you have a SAS date you can use monname format, I'm assuming it'll translate correctly but I could be wrong there.

[PierreYvesILY]

Hello Reeza,

I need the names of the months in German. I don't know if there's an option to set the function on a particular language?

That was originally the reason why I wrote the bloc with all names of the months.

[Reeza]

Is your SAS set to German for the language? If so then I assume it would take the German names.

Easy enough to check;

data _null_;
demo = put(today(), monname.);
put demo;
run;

And check your log.

The CATT() and CALL SYMPTUX() will fix the rest of your issues.

[PierreYvesILY]

Hello Reeza,

I just tried, and SAS is set up at ENGLISH.

Just bad luck. 😞

thanks

PY

[Reeza]

Well, if you're doing it more than once I'd recommend a format instead of using SELECT. It's reusable and more efficient in the long run.

If you're not familiar with formats, I suggest a paper called "Not Just Another Pretty Face". It does a great job at illustrating how it works.

[ballardw]

---

@PierreYvesILY wrote:

Hello Reeza,

I just tried, and SAS is set up at ENGLISH.

Just bad luck. 😞

thanks

PY

---

You can specify LOCALE for language options as you desire at run time. Specify the locale for the language, and then use one of the NL formats. This should generate just the month name.

options locale=German_Germany;
data _null_;
   date=mdy(2,14,2003);  
put month nldatemn.;
run;

[PierreYvesILY]

thanks a lot
have a ni ce WE
regards, py