topic Re: %SYSFUNC format problem in SAS Programming

%SYSFUNC format problem

sfmeier — Thu, 14 Jun 2012 15:23:01 GMT

Hi!

I am using the following macro within a proc format to generate a format. It is working perfectly. However, if I replace the format DDMMYYP10. by YYMMD9. it suddenly does not work anymore. How can I fix it?

&Stichdatum is a date, f.e. 31May2012

Thx, Stefan

--------------------------------------

/* Dynamische Generierung der Intervallgrenzen */

%Macro genInt();

value faellig

low - %SYSFUNC(Intnx(Month, &Stichdatum, 0, e)) = '0_abgelaufen'

%put Stichdatum=&stichdatum;

%LET dvar = &stichdatum;

%LET hvar = %SYSFUNC(Intnx(month, &dvar, 1, e));

/* Falls Monatsende nicht mit Quartalsende zusammenfällt */

%IF %sysfunc(intnx(month,&dvar,0,e)) < %sysfunc(intnx(qtr,&dvar,0,e))

%THEN

%DO;

%SYSFUNC(Intnx(Month, &dvar, 1, b)) - %SYSFUNC(Intnx(Qtr, &dvar, 0, e)) = %SYSFUNC(Intnx(qtr, &dvar, 0, end), DDMMYYP10.)

%END;

%LET dvar = %SYSFUNC(Intnx(qtr, &dvar, 1, b)); /* nächstes Quartal */

/* Quartalsscheiben erzeugen */

%DO %UNTIL (%SYSFUNC(Intnx(Qtr, &dvar, 0, e)) > %SYSFUNC(Intnx(year, &hvar, 1, e)));

%SYSFUNC(Intnx(qtr, &dvar, 0, b)) - %SYSFUNC(Intnx(Qtr, &dvar, 0, e)) = %SYSFUNC(Intnx(qtr, &dvar, 0, end), DDMMYYP10.)

%LET dvar = %SYSFUNC(Intnx(qtr, &dvar, 1, b)); /* nächstes Quartal */

%END;

/* Halbjahresscheiben erzeugen */

%DO %UNTIL (%SYSFUNC(Intnx(semiyear, &dvar, 0, e)) > %SYSFUNC(Intnx(year, &hvar, 2, e)));

%SYSFUNC(Intnx(semiyear, &dvar, 0, b)) - %SYSFUNC(Intnx(semiyear, &dvar, 0, e)) = %SYSFUNC(Intnx(semiyear, &dvar, 0, end), DDMMYYP10.)

%LET dvar = %SYSFUNC(Intnx(semiyear, &dvar, 1, b)); /* nächstes Halbjahr */

%END;

/* Jahresscheiben erzeugen */

%DO %UNTIL (%SYSFUNC(Intnx(year, &dvar, 0, e)) > %SYSFUNC(Intnx(year, &hvar, 6, e)));

%SYSFUNC(Intnx(year, &dvar, 0, b)) - %SYSFUNC(Intnx(year, &dvar, 0, e)) = %SYSFUNC(Intnx(year, &dvar, 0, end), YEAR4.)

%LET dvar = %SYSFUNC(Intnx(year, &dvar, 1, b)); /* nächstes Halbjahr */

%END;

/* Restscheibe erzeugen */

%SYSFUNC(Intnx(year, &dvar, 0, b)) - high = %SYSFUNC(Catx(%str( ),nach ,%SYSFUNC(Intnx(year, &dvar, -1, e), YEAR4.)))

;

%mend genInt;

Re: %SYSFUNC format problem

Astounding — Thu, 14 Jun 2012 16:08:58 GMT

Well, you have reasonably complex code, but haven't really told us what it means when you say it "doesn't work any more". So here's a wild guess.

The YYMMD formats really only require 7 characters, such as 2012-06. So by specifying YYMMD9, you are adding a couple of leading blanks to the result. Could that cause a problem? Would it help to switch to YYMMD7 instead of YYMMD9?

Good luck.

Re: %SYSFUNC format problem

bentleyj1 — Thu, 14 Jun 2012 16:25:06 GMT

When I run your code with MPRINT turned on, it shows this generated Value statement --

value faellig low - 19144 = '0_abgelaufen' 19145 - 19174 = 30.06.2012 19175 - 19266 = 30.09.2012 19267 -

19358 = 31.12.2012 19359 - 19448 = 31.03.2013 19449 - 19539 = 30.06.2013 19540 - 19631 = 30.09.2013 19632 - 19723 = 31.12.2013

19724 - 19904 = 30.06.2014 19905 - 20088 = 31.12.2014 20089 - 20453 = 2015 20454 - 20819 = 2016 20820 - 21184 = 2017 21185 -

21549 = 2018 21550 - high = nach 2018 ;

I think this is working because the formatted date such as 30.06.2012 is interpreted as a decimal(?), but the last value nach 2018 is clearly a character so I'm not sure about this explanation. But the format YYMMD9 returns a value with a dash in it, such as 2012-06, so it is obviously a character.

The solution I found is to put quote marks around the formatted value assignment.

= "%SYSFUNC(Intnx(qtr, &dvar, 0, end), YYMMD9.)"

and the value statement then returns this--

value faellig low - 19144 = '0_abgelaufen' 19145 - 19174 = " 2012-06" 19175 - 19266 = " 2012-09" 19267 -

19358 = " 2012-12" 19359 - 19448 = " 2013-03" 19449 - 19539 = " 2013-06" 19540 - 19631 = " 2013-09" 19632 - 19723 = "

2013-12" 19724 - 19904 = " 2014-06" 19905 - 20088 = " 2014-12" 20089 - 20453 = "2015" 20454 - 20819 = "2016" 20820 - 21184 =

"2017" 21185 - 21549 = "2018" 21550 - high = "nach 2018" ;

Please test it and let us know if it solves the problem.

John

Re: %SYSFUNC format problem

Ksharp — Fri, 15 Jun 2012 02:37:15 GMT

Steffan,

Can you explain what you want ?

As to your code, It is not look good. I think you can first make a dataset to hold these date range and make such FORMAT .

About your question, I notice a problem ,You should use quote around the label you make ,since it is character label.

%SYSFUNC(Intnx(semiyear, &dvar, 0, end), DDMMYYP10.)

--------->

" %SYSFUNC(Intnx(semiyear, &dvar, 0, end), DDMMYYP10.) "

%SYSFUNC(Intnx(qtr, &dvar, 0, end), DDMMYYP10.)

------>

" %SYSFUNC(Intnx(qtr, &dvar, 0, end), DDMMYYP10.) "

..... and so on.

Ksharp

Re: %SYSFUNC format problem

sfmeier — Fri, 15 Jun 2012 07:21:08 GMT

Hi Ksharp,

thank you for your answer, I would love to make my code to look better!

I want to create a date format that depends on the report date (which the user can specify). Two format "components" should depend on the report date:

The range and number of the "categories" and the "label", i.e.

01.07.12-30.09.12 = 2012-09

I'd to have a macro to create a table and then read the table in proc format. However I have no idea how to read a table into a proc format.

Cheers,

Stefan

Re: %SYSFUNC format problem

Alpay — Fri, 15 Jun 2012 08:13:50 GMT

What you are doing in above code can be programmed in a data step.

Put character arguments of INTNX function in quotes and reference variables by their name in data step instead of macro variable names.

e.g: Intnx(month, &dvar, 1, e) vs Intnx('month', dvar, 1, 'e')

Get your macro variable's (Stichdatum) value into a variable.

Assign values to dvar and hvar variables.

Store your format name 'faellig' in variable called fmtname and retain it in your control data set.

For low and high end of intervals, use 'l' and 'h' values for hlo variable, respectively.

Store your left end and right end values of intervals in Start and End variables (values separated by a dash on the left side of equal sign in proc format statement; e.g.:

20089 - 20453 = "2015").

Store your label in Label variable (e.g.: "2015").

Issue output statements after each interval constructed.

Construct do until loops to generate additional rows.

You can read in this data set by using cntlin option in proc format.

%macro genInt(Stichdatum);

data datefmt;

if _n_ = 1 then do;

Stichdatum = INPUT(SYMGET('Stichdatum'),anydtdte.);

dvar = stichdatum;

hvar = Intnx('month', dvar, 1, 'e');

end;

retain fmtname 'dtfmt';

If intnx('month',dvar,0,'e') < intnx('qtr',dvar,0,'e') then do;

hlo = 'l';

Start = .;

End = INTNX('Month',Stichdatum,0,'e');

Label = '0_abgelaufen';

output;

end;

hlo = ' ';

dvar = Intnx('qtr', dvar, 1, 'b');

DO UNTIL (Intnx('Qtr', dvar, 0, 'e') > Intnx('year', hvar, 1, 'e'));

Start = Intnx('qtr', dvar, 0, 'b');

End = Intnx('Qtr', dvar, 0, 'e');

Label = PUT(Intnx('qtr', dvar, 0, 'end'), DDMMYYP10.);

output;

dvar = Intnx('qtr', dvar, 1, 'b');

END;

DO UNTIL (Intnx('semiyear', dvar, 0, 'e') > Intnx('year', hvar, 2, 'e'));

Start = Intnx('semiyear', dvar, 0, 'b');

End = Intnx('semiyear', dvar, 0, 'e');

Label = PUT(Intnx('semiyear', dvar, 0, 'end'), DDMMYYP10.);

output;

dvar = Intnx('semiyear', dvar, 1, 'b');

END;

DO UNTIL (Intnx('year', dvar, 0, 'e') > Intnx('year', hvar, 6, 'e'));

Start = Intnx('year', dvar, 0, 'b');

End = Intnx('year', dvar, 0, 'e');

Label = PUT(Intnx('year', dvar, 0, 'end'), YEAR4.);

output;

dvar = Intnx('year', dvar, 1, 'b');

END;

Start = Intnx('year', dvar, 0, 'b');

End = .;

hlo = 'h';

Label = Catx(' ','nach' ,PUT(Intnx('year', dvar, -1, 'e'), YEAR4.));

output;

format Start End Date9.;

keep fmtname Start End hlo Label;

run;

proc format cntlin = datefmt; run;

%mend;

%genInt(31May2012);