SAS format in data step

Question

I have a WORK dataset with more than 30 columns but only 2 columns out of them are date fields. (Start date and End date). I want the date format in the permanent dataset to be in date. and not in yymmdd10. which is the current format in work dataset. When I used the below code, the two date fields are taking first two positions. I dont want to reorder the positions and at the same time dont want to mention the format with all 30+ columns. Could someone please help me if there is any way for this?

data DLR.DEALER; 
set  work.dealer_invoices; * this dataset contains more than 30 columns; 
format start_dt end_dt date.; 
run;

I could not find any solution for this on our site. Any help is highly appreciated than just asking me to mention all the columns in the format statement :) Thanks in advance.

I wouldn't think the format statement would have any effect on variable ordering as it is after the set statement. I can't replicate your problem with the information given. — DWal
Same, can't replicate your issue. If you can replicate it, I recommend opening up a ticket with tech support. — Reeza
@DWal thats really surprising. I think, then I should raise a ticket. Thank you — Naga Vemprala
And you checked the log to make sure it ran? And didn't have the dataset open (effectively locking it and preventing changes)? Did you try restarting your SAS session? I think I'd have to see this bug replicated to believe it. — Robert Penridge
I do not relly get this. You are mentioning that you do not want start_dt and end_dt to be the first columns in your DLR.DEALER data set, right? Then you belive that the format line changes the order from the original order in work.dealer_invoices, but in the same time you say that the result of proc contents data=work.dealer_invoices; run; lists start_dt and end_dt as numer one and two respecitvely in Alphabetic List of Variables and Attributes. Does this not mean that they are first in this dataset from the beginning? Ergo, no change in order. — D. Josefsson

Joe Joe · Accepted Answer · 2015-05-04T19:36:13

Certainly the format statement shouldn't have any impact on ordering given its location.

A workaround would be to use PROC DATASETS to change the format instead of in the data step.

You also could "mention all columns" fairly easily.

proc sql;
  select name into :namelist separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='DEALER_INVOICES'
  order by varnum;
quit;

then

data DLR.DEALER;
  retain &namelist;
  set work.dealer_invoices;
  format...;
run;

SAS format in data step

1 Answers