BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

I have written a lot of macros over the past that utilize pipe commands as in the code box below.  The date from the dir commands has always been in MM/DD/YYYY for me.  This has worked fine for quite some time because everyone that uses my code is either in the US or India, and we have all had our computers set to this format.  However, we have been bringing in users from the UK, and they all set their systems to be DD/MM/YYYY.  When they run my code, this pipe command swaps the day and month, and then I get invalid notes in my log because they don't consider 29/04/2025 to be a valid date.  Visually, I can tell that this is April 29, but since my code is set up to read it as MM/DD/YYYY, it doesn't know what the 29th month is.

 

With all that background, is there a way to easily tell which format the value is in?  I can set up some IF statements such that if the first 2 digits are greater than 12, then I know it's in DD/MM/YYYY format, or if the second 2 digits are greater than 12, then I know it's in MM/DD/YYYY format.  However, if both are less than or equal to 12, I won't know.  04/03/2025 could be March 4 or April 3.  Has anyone dealt with this scenario before?

 

filename SASlist pipe "dir /t:w &datapath.\*.sas7bdat";

data datadir;
	infile SASlist dlm="¬";
	length buff $2000;
	input buff;
	if _n_>3 & index(buff,"File")=0 & index(buff,"Dir")=0 then do;
		date=input(scan(buff,1,''),mmddyy10.);
		file=scan(buff,-1,'');
		dset=upcase(scan(file,1,'.'));
	end;
	format date date9.;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

The DATESTYLE= option will control how the ANYDT... informats handle the ambiguous date strings.

 

Try it yourself.

data have;
  input name $ string :$10. ;
cards;
MDY 1/31/2025
DMY 31/1/2025
??? 2/6/2025
;

%let datestyle=%sysfunc(getoption(datestyle));
%put &=datestyle;

options datestyle=MDY;
data mdy;
  set have;
  mdy = input(string,anydtdte10.);
  format mdy date9.;
run;

options datestyle=DMY;
data dmy;
  set have;
  dmy = input(string,anydtdte10.);
  format dmy date9.;
run;

options datastyle=&datestyle;

data both;
  merge mdy dmy ;
run;

proc print;
run;

Tom_0-1746195705926.png

 

The trick is know which setting to use.  That is a Windows question, not a SAS question.

 

Although if your SAS session is using a similar locale as your Windows system (or is it the Windows file system that controls how DIR formats its output?) then using DATESTYLE=LOCALE might help.

View solution in original post

8 REPLIES 8
quickbluefish
Barite | Level 11

Yeah, as @Kurt_Bremser suggests, I think you might want to make this a two step process where you first pipe this command to the shell:

Get-WinSystemLocale

...read in the output, and make your determination based on that.  You can try this in Powershell just to see the output.

Ksharp
Super User

You could try option datastyle= and using informat anydtdte32. in input()  function.

option datestyle=dmy;
.........
date=input(scan(buff,1,''),anydtdte32.);

djbateman
Lapis Lazuli | Level 10

@Ksharp , I didn't have much luck with that method.  You can see below that it properly got March 31, but I changed Feb 6 and May 2 to June 2 and Feb 5, respectively.

Picture1.png

Tom
Super User Tom
Super User

The DATESTYLE= option will control how the ANYDT... informats handle the ambiguous date strings.

 

Try it yourself.

data have;
  input name $ string :$10. ;
cards;
MDY 1/31/2025
DMY 31/1/2025
??? 2/6/2025
;

%let datestyle=%sysfunc(getoption(datestyle));
%put &=datestyle;

options datestyle=MDY;
data mdy;
  set have;
  mdy = input(string,anydtdte10.);
  format mdy date9.;
run;

options datestyle=DMY;
data dmy;
  set have;
  dmy = input(string,anydtdte10.);
  format dmy date9.;
run;

options datastyle=&datestyle;

data both;
  merge mdy dmy ;
run;

proc print;
run;

Tom_0-1746195705926.png

 

The trick is know which setting to use.  That is a Windows question, not a SAS question.

 

Although if your SAS session is using a similar locale as your Windows system (or is it the Windows file system that controls how DIR formats its output?) then using DATESTYLE=LOCALE might help.

djbateman
Lapis Lazuli | Level 10

I think I can work with this.  Thank you for the direction!

Tom
Super User Tom
Super User

Sounds like a question for a Windows forum.

 

You could skip the PIPE and just use SAS code to get the filenames.

You might try this macro https://github.com/sasutils/macros/blob/master/dirtree.sas

It might not have your problem since it uses the NLDATM informat to read the lastmod timestamp.

data_null__
Jade | Level 19

Maybe this would work for you

 

 

reg query "HKCU\Control Panel\International" /v sShortDate

HKEY_CURRENT_USER\Control Panel\International
    sShortDate    REG_SZ    M/d/yyyy

 

You can use INFILE statement option FILEVAR to execute multiple PIPEed commands.  

 

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 920 views
  • 5 likes
  • 6 in conversation
OSZAR »