Issue with string to date type conversion in proc sql statement

[msale1]

Hello, I have written a program that imports multiple CSV files in a directory and merges them all into one SAS data file.  I think I have that part working properly.  However, one of the things I need to do is  and create a new column in the destination merged data set that takes the date in the file name being imported (example file name: 6-8-2021.csv) and stores that value into a new column.  I have been able to strip the ".csv" from the file name.  My issue is that when I try to convert the newly stripped file name (e.g. 6-8-2021) to a date format, it doesn't work properly.  I have tried multiple different syntaxes, but I must be making a silly mistake.  I suspect something has to do with the fact that my filenames don't have leading zeroes for single digit months or days.

The program is below.  The PROC SQL update statement is where I'm trying to do what I indicated above. I have tried using PUT and INPUT, but was always under the impression that PUT always creates string variable, so I abandoned that.  Right now, this program inserts nothing into the column.  Any assistance would be greatly appreciated.  Thanks so much. (I've attached the two files I'm trying to import as a proof of concept)

%macro MultImp(dir=,out=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
filenamenosuff = tranwrd(myfiles,'.csv','');
putlog 'WARNING: the filename is ' filenamenosuff=;
out="&out";
drop myfiles;
call execute('
  proc import dbms=csv out= _test
            datafile= '||fname||' replace ;
  run;
    proc append data=_test base='||out||' force; run;
 	proc delete data=_test; run;
	proc sql; alter table sasdata.merged add FNDate num format=mmddyyd10.; quit;
	proc sql; update sasdata.merged set FNDate = '||input(filenamenosuff,mmddyyd10.)||' where FNDate IS NULL; quit;
');
run;
filename myfiles clear;

%mend;

%MultImp(dir=C:\Users\micha\Desktop\rand,out=sasdata.merged);

[PaigeMiller · Accepted solution]

Did you look at the LOG?

data a;
filenamenosuff='6-8-2021';
date=input(filenamenosuff,mmddyyd10.);
run;

produces this log

 73         data a;
 74         filenamenosuff='6-8-2021';
 75         date=input(filenamenosuff,mmddyyd10.);
                                      __________
                                      485
 NOTE 485-185: Informat MMDDYYD was not found or could not be loaded.
 
 76         run;

I think that's pretty clear. The informat MMDDYYD does not exist.

You could use informat MMDDYY

[Sajid01]

Hello
This is one way to get the date in the desired format.

data a;
filenamenosuff='6-8-2021';
date=put(input(filenamenosuff,Anydtdte10.),mmddyyd10.);
put date= ;
run;
/* you will see this in the log 
date=06-08-2021 */

[andreas_lds]

---

@Sajid01 wrote:

Hello
This is one way to get the date in the desired format.

data a;
filenamenosuff='6-8-2021';
date=put(input(filenamenosuff,Anydtdte10.),mmddyyd10.);
put date= ;
run;
/* you will see this in the log 
date=06-08-2021 */

---

But you don't create a date-variable, but a string looking like a one.

[Sajid01]

Pretty simple; See below.
Earlier it was a variable of type character and now numeric.

data a;
Format date mmddyyd10.;
filenamenosuff='6-8-2021';
date=input(filenamenosuff,Anydtdte10.);
put date= ;
run;
/*  date=06-08-2021
*/

[msale1]

Thank you for your assistance. This worked. I must have been looking at the wrong date format lists.

[Tom]

---

@msale1 wrote:
Thank you for your assistance. This worked. I must have been looking at the wrong date format lists.

---

Your were looking a FORMAT list instead of looking at an INFORMAT list.

FORMAT is used to convert values to text.

INFORMAT is used to convert text to values.

[Kurt_Bremser]

So you basically want to read all csv files in a directory into one dataset?