topic Date format understanding in SAS Programming

Date format understanding

Babloo — Fri, 20 Dec 2019 10:25:20 GMT

Could you please help me to resolve the error?

Value of macro variable is defined as

%let REPORTINGDATE =15886;

649        proc sql;
650           651           select CASE
652        WHEN PROFITABILITY_FLG= "" then .
653        WHEN PROFITABILITY_FLG= "O" then input(put("&REPORTINGDATE.",$5.),yymmd.)
WARNING: Apparent symbolic reference REPORTINGDATE not resolved.
654        WHEN PROFITABILITY_FLG ne "O" then min(input(PREMIUM,anydtdte7.),input(COVERAGE,anydtdte7.)
654      ! )
655        END from work.temp;
ERROR: The informat YYMMD was not found or could not be loaded.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

I want the Output value as 30JUN2003 and it should be numeric value when PROFITABILITY_FLG equals "O"

Re: Date format understanding

ed_sas_member — Fri, 20 Dec 2019 10:36:28 GMT

Hi @Babloo

Do you mean applying a different format to the new variable according to the flag value ?

As far as I know, that's not possible, as a format is a variable attribute.

Re: Date format understanding

Babloo — Fri, 20 Dec 2019 10:46:40 GMT

yes, I want to apply a Format based on the Input values. Above log message is from SAS DI Studio. However when I run the code is EG, it is working as excepted. Not sure how to make it to work in DI Studio.

data test;
input FLG $ ;
datalines;
O 
O  
P 
;
run;

%let REPORTINGDATE=15886; /*corresponding date is 20030630*/

proc sql;
create table output as select *,CASE   
WHEN FLG= "" then . 
WHEN FLG= "O" then input("&REPORTINGDATE.",yymmd.)
END as IR format=date9. from test;
run;

Output:

FLG	IR
O	30. Jun 03
O	30. Jun 03
P	.

Re: Date format understanding

PaigeMiller — Fri, 20 Dec 2019 13:17:26 GMT

WARNING: Apparent symbolic reference REPORTINGDATE not resolved.

You have not defined the macro variable REPORTINGDATE at the time it is to be used. I know you think you have assigned a value to &REPORTINGDATE, but SAS does not think you have done so (and you haven't shown us the portion of the code where you do this assigning) and so, SAS can't process any code involving &REPORTINGDATE.

WHEN PROFITABILITY_FLG= "O" then input(put("&REPORTINGDATE.",$5.),yymmd.)

Assuming reporting date is properly defined, it this completely unnecessary to go through these input(put()) gyrations with a macro variable. If the macro variable has value 15886, and this is a SAS date, then you assign the value to the variable in PROC SQL and then format it. Thus the code should look like this:

WHEN PROFITABILITY_FLG= "O" then &reportingdate

Why? Because you can assign a value of 15886 to a numeric variable, you cannot assign a formatted value such as 19/12/30 to a numeric variable. When you assign a value of 15886, then you can assign a format to this variable.

Also

WHEN PROFITABILITY_FLG= "O" then input(put("&REPORTINGDATE.",$5.),yymmd.)
ERROR: The informat YYMMD was not found or could not be loaded.

Can you spot the error here?