topic Re: Issue with string to date type conversion in proc sql statement in SAS Programming

Issue with string to date type conversion in proc sql statement

msale1 — Tue, 08 Jun 2021 22:18:28 GMT

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

Re: Issue with string to date type conversion in proc sql statement

PaigeMiller — Tue, 08 Jun 2021 23:25:06 GMT

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

Re: Issue with string to date type conversion in proc sql statement

Sajid01 — Wed, 09 Jun 2021 01:16:03 GMT

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 */

Re: Issue with string to date type conversion in proc sql statement

andreas_lds — Wed, 09 Jun 2021 05:16:07 GMT

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

Re: Issue with string to date type conversion in proc sql statement

Sajid01 — Wed, 09 Jun 2021 11:53:35 GMT

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
*/

Re: Issue with string to date type conversion in proc sql statement

Kurt_Bremser — Wed, 09 Jun 2021 14:35:17 GMT

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

Re: Issue with string to date type conversion in proc sql statement

msale1 — Sat, 12 Jun 2021 15:15:20 GMT

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

Re: Issue with string to date type conversion in proc sql statement

Tom — Sat, 12 Jun 2021 15:20:24 GMT

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