topic Re: Appending SAS function while resolving the macro variable in SAS Programming

Appending SAS function while resolving the macro variable

David_Billa — Tue, 29 Oct 2019 12:31:47 GMT

I've a macro variable which resolves as follows.

	%put &business_key;

CASHFLOW_DT|| '  |  ' || CASHFLOW_CD|| '  |  ' || NAME

If there is _DT in the value of the macro variable then either I need the macro variable to resolve as below or recreate another macro variable as shown below. _DT value can come at any position or sometimes it will not come as it depends on source

cats(put(input(CASHFLOW_DT, 8.), date9.)|| '  |  ' || CASHFLOW_CD|| '  |  ' || NAME

Thanks in advance

Re: Appending SAS function while resolving the macro variable

mkeintz — Tue, 29 Oct 2019 14:03:16 GMT

You are submitting the variable CASHFLOW_DT to an input function in the expression

input(CASHFLOW_DT, 8.)

This means the CASHFLOW_DT is a character variable. And the character value is a series of numeric characters representing the number of days after 01jan1960. I doubt that is your situation.

Please give some example of the values of the constituent variables, and what the corresponding business key would produce if executed.

Re: Appending SAS function while resolving the macro variable

Tom — Tue, 29 Oct 2019 16:59:32 GMT

What is the CASHFLOW_DT variable?

Is it a numeric variable that contains date values (number of days since 1960)? If so then want PUT(CASHFLOW_DT,DATE9.) instead.

Is it a character variable that contains values that look like dates, like 2018-07-31 or 07/31/2018, then use the appropriate informat for the type of date strings it has. PUT(input(CASHFLOW_DT,YYMMDD10.),DATE9.)

Is it a character variable that contains date value as a string of digits, like 21396 which would be '31JUL2018'd, then add an input() function call. PUT(input(CASHFLOW_DT,32.),DATE9.)

Is it a numeric variable that contains numbers that look like dates in either YMD order, like 20180731, or MDY order like 07312018, or DMY order like 31072018 then you will need a triple sandwich to convert the number to a string, then to date and finally back to string. PUT(input(PUT(CASHFLOW_DT,z8.),YYMMDD8.),DATE9.)

If it is a number with date values and already has the DATE9. format attached to it and you are using the generated code in a data step then use the VVALUE() function. VVALUE(CASHFLOW_DT).

Whatever syntax you need to generate just use the TRANWRD() function to convert your existing string to the new string.

%let new_business_key=%sysfunc(tranwrd(&business_key),VVALUE(CASHFLOW_DT),CASHFLOW_DT));

Re: Appending SAS function while resolving the macro variable

Astounding — Tue, 29 Oct 2019 23:40:02 GMT

I'm going to guess that you are merely trying to change the formula, but not computing anything yet. Only when the macro variable gets used at a later point will you compute anything. If that's right, then you could use:


data _null_;
if index("&business_key", '_DT') then call symput 

('business_key', "cats(put(input(CASHFLOW_DT, 8.), date9.)|| '  |  ' || CASHFLOW_CD|| '  |  ' || NAME" ) ;

run;

That replaces &business_key. If you want a different macro variable instead, change the first parameter to CALL SYMPUT.

Re: Appending SAS function while resolving the macro variable

s_lassen — Wed, 30 Oct 2019 11:28:31 GMT

Sounds quite simple, just use a TRANWRD function:

%let business_key=CASHFLOW_DT|| '  |  ' || CASHFLOW_CD|| '  |  ' || NAME;
%put &business_key;
%let business_key=%sysfunc(tranwrd(&Business_key,CASHFLOW_DT,put(input(CASHFLOW_DT, 8.), date9.)));
%put &business_key;

Although I do not understand where the CATS function in your example comes in, am I missing something?

Re: Appending SAS function while resolving the macro variable

David_Billa — Wed, 30 Oct 2019 14:22:19 GMT

Instead of hardcoding like 'CASHFLOW_DT', can we do with wildcards like
%'_DT'%? Because it might be any variable which ends with _DT where I want
to implement this mechanism.