topic Re: Format and Informat Statements With Empty Dataset in SAS Programming

Format and Informat Statements With Empty Dataset

shl007 — Wed, 02 Aug 2023 19:59:14 GMT

When specifying a format and informat statement on columns in an EMPTY dataset, I'm finding the format and informat don't stick. Is there a way around this issue? I can understand if it's working as designed but am wondering if there is a way to set format and informat on an empty dataset column.

More specifically, the empty dataset is in a "create view" proc sql UNION statement, snippet below. The empty dummy placeholders in the first union select, I believe, is causing the format/informat not to stick? The second union select DOES have data in those columns.

PROC SQL;

CREATE VIEW AS

SELECT

'-' as field1,
'-' as field2,
0 as field3,

FROM TABLE1
union
SELECT

field1,

field2,

field3

FROM TABLE2

;

--> RESULT: even though field1, field2, and field3 are defined with specific FORMAT and INFORMAT settings in union select #2, those FORMAT and INFORMAT settings don't carry over to the final view.

Re: Format and Informat Statements With Empty Dataset

ballardw — Wed, 02 Aug 2023 20:14:32 GMT

Define "empty dataset".

I don't see any attempt to create a data set. The code creates a VIEW (which is not named) which is quite different in some respects. It in effect reruns the code when the View is USED. So if you do not apply a changed format or informat in the steps that create a View then you get the properties at the time the code next executes.

What code did you attempt to apply format and informat? I don't see any attempt in your code.

Did the format or informat type agree with the variable type?

Show the log with the code and any notes.

Both the the data steps below create data sets with no observations and successfully assign formats and informats so it is not a limitation of a data set not allow setting formats or other properties for variables.

data junk;
  set sashelp.class (obs=0);
  format age F32.12;
  informat weight f5.;
run;

data newjunk;
   input x y z;
   format x F18. y Date9. ;
   informat z mmddyy10.;
datalines;
;

Re: Format and Informat Statements With Empty Dataset

sbxkoenk — Wed, 02 Aug 2023 20:03:21 GMT

For me format and informat stick !

data work.have;
input testcol $;
format testcol $50.;
attrib testcol informat=$50.; 
datalines;
run;

proc contents; run;

Koen

Re: Format and Informat Statements With Empty Dataset

shl007 — Wed, 02 Aug 2023 20:07:58 GMT

I think I solved the above issue by ensuring my 2nd union select was FIRST so that the view respected the informats and formats from the 2nd select!

Re: Format and Informat Statements With Empty Dataset

Tom — Wed, 02 Aug 2023 20:23:48 GMT

Are you asking what rules PROC SQL uses to decide which FORMAT (or INFORMAT) to attach to a variable that is sourced from multiple datasets?

I know for a DATA STEP it will attach the first (non empty) format that it sees.

Run some tests with PROC SQL and find out for yourself. Make sure to include some variables that have no format and/or no informat attached at all.

Your description seems to say that for PROC SQL is uses the LAST one it sees. Or perhaps it picks the FIRST but that it processes the different contributing datasets in a bottom up (or LIFO, last in first out) order.