topic Re: Assign same format to multiple variables that end with DT in SAS Programming

Assign same format to multiple variables that end with DT

mariko5797 — Thu, 07 Oct 2021 18:17:54 GMT

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.

Re: Assign same format to multiple variables that end with DT

ballardw — Thu, 07 Oct 2021 19:14:02 GMT

@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.

Re: Assign same format to multiple variables that end with DT

maguiremq — Thu, 07 Oct 2021 19:35:06 GMT

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.

Re: Assign same format to multiple variables that end with DT

mariko5797 — Thu, 07 Oct 2021 19:43:18 GMT

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

Re: Assign same format to multiple variables that end with DT

mariko5797 — Thu, 07 Oct 2021 19:47:40 GMT

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

Re: Assign same format to multiple variables that end with DT

maguiremq — Thu, 07 Oct 2021 19:52:55 GMT

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