Assign same format to multiple variables that end with DT

[mariko5797]

Is there a way to have all variables that end with DT to be formatted DATE9., all variables that end with TM to be formatted TIME5., and all variables ending in DTM to be formatted DATETIME16.?

I tried:

format :sdt date9.;

But FORMAT is no longer colored blue with the colon there.

[ballardw]

---

@mariko5797 wrote:

Is there a way to have all variables that end with DT to be formatted DATE9., all variables that end with TM to be formatted TIME5., and all variables ending in DTM to be formatted DATETIME16.?

I tried:

format :sdt date9.;

But FORMAT is no longer colored blue with the colon there.

---

If you think that you want to use short hand lists then do so when creating the variables. You can't use a list that starts with ":"  . Name the variables starting with DT and you can use DT: with them, similar with your TM or DTM, start the names that way.

Or if the variables are in adjacent columns you can use a list with 2 dashes:  FirstDT -- TheLastDT  . But that only will work if they are in adjacent columns of the data set.

Another way would be get the names of the variables into a data step and use that to write Proc Datasets code to assign a format (or other property) after the data set is created.

[mariko5797]

Ah I see... Unfortunately this dataset is strict on naming and ordering of variables. 

[maguiremq · Accepted solution]

If @ballardw 's solution doesn't work for you (it's the logical way and is what I would suggest it, too), this is one way I've dealt with the issue if I have many variables scattered across the data set.

You essentially extract the information from the dictionary tables within SAS which provide meta-level information about the data sets. An example data set:

data have;
input id val1 val2 start_dt :mmddyy10. end_dt :mmddyy10.;
datalines;
1 2 3 11/20/2020 11/13/2021
;
run;

Which results in:

Obs id val1 val2 start_dt end_dt 
1 1 2 3 22239 22597 

We have start_dt and end_dt at the end. As @ballardw mentioned, you can use the double hyphen to take care of it here, but I don't know if that's how your data are structured. So, we can extract all those variable names that contain `dt`.

proc sql noprint;
	select
				name
					into: date_vars separated by " "
	from
				dictionary.columns
	where
				upcase(libname) = "WORK" and upcase(memname) = "HAVE" and upcase(name) contains ("DT");
quit;

Note, I used the `upcase` function to reduce any potential issues if you re-ran this code. This extracts those variables (e.g. `name`) in the WORK.HAVE data set that contain "dt". It stores them in a single macro variable, `date_vars`, where they are separated by a single space.

I can then run this to apply the format to those variables.

proc print data = have;
format &date_vars. date9.;
run;

Obs id val1 val2 start_dt end_dt 
1 1 2 3 20NOV2020 13NOV2021 

I forgot to add that there may be additional complexities in your variable names that I didn't account for. If there are other variables with "dt" in them, they will be picked up here, too. We may need to use another function to get exactly what you need.

[mariko5797]

What is dictionary.columns from? Is that build into SAS?

[maguiremq]

It basically stores higher level information about variables and their attributes within a data set. I'm probably not explaining that well, but here's something that explains it better than I can. It is active whenever you start a session.

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/070-30.pdf