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.
CREATE VIEW AS
'-' as field1,
'-' as field2,
0 as field3,
--> 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.
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; ;
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.