BookmarkSubscribeRSS Feed
shl007
Obsidian | Level 7

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.

4 REPLIES 4
ballardw
Super User

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;
;

 

sbxkoenk
SAS Super FREQ

For me format and informat stick !

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

proc contents; run;

Koen

shl007
Obsidian | Level 7
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!
Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 237 views
  • 2 likes
  • 4 in conversation