topic Re: PROC APPEND - trying to suppress FORMAT warnings in SAS Programming

PROC APPEND - trying to suppress FORMAT warnings

EvoluZion3 — Thu, 19 Dec 2019 11:14:26 GMT

Hi. I'm using a PROC APPEND to insert some records from SAS (originally copied over from SQL Server) into a Postgres table.

The source data in SQL Server has non-ASCII characters, so Tom has helped me in a recent thread to load the data into SAS and convert from Latin9 to UTF-8, ready to be inserted into Postgres.

This works fine as long as I use a PROC APPEND, however for each date field I get: WARNING: Variable myDate has format 'DATETIME25.'n on the BASE data set and format 'DATETIME22.'n on the DATA data set.

I don't think I can fix this properly, as the source and destination are respectively SQL Server and Postgres, and obviously they don't understand what a DATETIME25 is, nor can I change them.

How can I suppress these warnings from the log, as they do not affect the underlying data in any way.

Re: PROC APPEND - trying to suppress FORMAT warnings

Rick_SAS — Thu, 19 Dec 2019 12:09:24 GMT

Specify the format of the variable by using the FORMAT statement inside the call to the APPEND procedure.

data A;
format d datetime22.;
d = '01JAN2020:00:00:00'dt;
run;

data B;
format d datetime25.;
d = '02JAN2020:01:23:45'dt;
run;

proc append base=A data=B; 
format d datetime22.;   /* <== specify the 'base' format */
run;

proc print; run;

Re: PROC APPEND - trying to suppress FORMAT warnings

EvoluZion3 — Thu, 19 Dec 2019 12:40:43 GMT

Thanks Rick, I can get that code working when I "hard-code" the formats.

The wider problem I have (which admittedly I haven't elaborated on yet) is that I'm writing a SAS macro function (APPEND_TO_POSTGRES) sharable within my team, so developers/analysts can call my macro to append data across. My macro takes care of the UTF8 conversion etc., and one or two other things which our team may find useful, but obviously there will be different datasets coming in with different formats.

My current solution then is to use the DICTIONARY table to identify the formats of the fields in the destination table on Postgres, the name of which is passed in as a parameter into my macro, iterate through each field, dynamically build a DATA STEP macro variable similar to that which you posted above, populated with any fields who's format needs to change, and then execute the macro variable.

I think this will work, but I was hoping there was a more simple solution.

Re: PROC APPEND - trying to suppress FORMAT warnings

Rick_SAS — Thu, 19 Dec 2019 13:01:04 GMT

Presumably, you know what formats you want for the final data set? If so, a simpler way might be to simply remove all formats until all the data sets are appended. Then use PROC DATASET to set the formats for the combined data.

Re: PROC APPEND - trying to suppress FORMAT warnings

EvoluZion3 — Thu, 19 Dec 2019 13:43:41 GMT

That's the problem really, I don't. Different developers in my team will be pushing data from different tables from different sources into different Postgres tables, and so the formats could be anything.

But if I use the DICTIONARY table, I can read the formats of the intended destination tables, and modify the formats of the intermediary SAS table in-flight to match the destination, then I can append without warnings.

That's the plan.

Re: PROC APPEND - trying to suppress FORMAT warnings

Cargo789 — Mon, 06 Dec 2021 20:37:40 GMT

proc append base = table_cible

data = table_source
force
;
format date_abc date_xyz; /* Here, define an empty format for the date variables */
run;

Re: PROC APPEND - trying to suppress FORMAT warnings

ChrisNZ — Tue, 07 Dec 2021 04:33:08 GMT

If you remove formats before appending, do you still get the warning?

This way no need to to guess (though I'd hope the Postgres formats follow a norm, so you shouldn't need to guess).

Re: PROC APPEND - trying to suppress FORMAT warnings

Cargo789 — Tue, 07 Dec 2021 13:55:20 GMT

Before the proc append, the dates format are different between variables in the source and target datasets. I don't know if it is possible to remove a format before, but you can redefine them with the same format. To skip the warning messages into then Proc Append, I guess the best way is to remove the format inside the Proc Append.