BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
msale1
Calcite | Level 5

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);

  

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Sajid01
Meteorite | Level 14

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
Jade | Level 19

@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
Meteorite | Level 14

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
Calcite | Level 5
Thank you for your assistance. This worked. I must have been looking at the wrong date format lists.
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1512 views
  • 0 likes
  • 6 in conversation