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);
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
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
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 */
@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.
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 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.
So you basically want to read all csv files in a directory into one dataset?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: