topic Re: Date variable is always converted to 01/01/1960 value. in SAS Programming

Date variable is always converted to 01/01/1960 value.

jayakumarmm — Tue, 18 Oct 2016 19:39:17 GMT

Hi,

I am macro date variable is always converted to 01/01/1960, In the below Received_date is the variable where I am tryng to assign the macro variable. &file_dt

filename fnames pipe 'ls /usr/path/*.xlsx';


data fnames;
    infile fnames pad missover;
    input @1 filename $255.;
	year = scan(filename, -2);
	day = scan(filename, -3);
	mon = substr(scan(filename, -4),1,3);
	con = catx("/",day,mon,year);
   file_received_dt=input(catx("/",day,mon,year),date11.);
   format file_received_dt mmddyy10.;
    n=_n_;
run;

proc sql noprint; select count(filename) into :num from fnames; quit;

%macro file_process;
    %do i=3 %to &num;

        proc sql noprint;
            select strip(filename),file_received_dt into :filename, :file_received_dt  from fnames where n=&i;
        quit;

	%let file_proc = &filename;
	%let file_dt = &file_received_dt;

	libname xlsFile XLSX "&file_proc";
	options validvarname=v7;
	options SYMBOLGEN MPRINT;
	
	PROC SQL;
    	create table  work.data_raw as 
	(select * from xlsFile.'Sheet1$A3:AD2000'n where col1 is not null);
	quit;
	data work.data(rename=(number=number_1));
	set work.data_raw;
	if find(dob,".") Then 
	do;
	dob = substr(dob,1,6);
	end;
	if length(dob) >= 10 Then 
	do;
	format dob_Dt mmddyy10.;
	dob_Dt = dob;
	end;
	else
	do;
	dob_Dt = substr(dob,1,6) - 21916;
	 format dob_Dt mmddyy10.; 
	end;
	format Load_dt mmddyy10.;
	format Received_dt mmddyy10.;
	data_dt = put((INTNX('month', %sysfunc(today()), -2, 'B')),monyy5.);
	Load_dt = Today();

	Received_dt = &file_dt;

	sam_mod = put(sam, $8.);
	track_num_mod = input(col1, 8.);
	run;

	data work.data1(drop= col1 dob);
	set work.data;
	run;

	PROC SQL;
    	create table  work.data_raw_2 as 
	(select * from xlsFile.'transmittal tab$A3:L2000'n);
	quit;
	data work.data2;
	set work.data_raw_2;
	format Load_dt mmddyy10.;
	format Received_dt mmddyy10.;
	data_dt = put((INTNX('month', %sysfunc(today()), -2, 'B')),monyy5.);
	Load_dt = Today();

	Received_dt =&file_dt;

	run;
    %end;
%mend;

%file_process;

Re: Date variable is always converted to 01/01/1960 value.

SASKiwi — Tue, 18 Oct 2016 18:55:32 GMT

Try this:

file_received_dt= mdy(mon,day,year);

Re: Date variable is always converted to 01/01/1960 value.

jayakumarmm — Tue, 18 Oct 2016 19:01:22 GMT

I have tried this but it is not working.I am able to get the exact value in macro variable &file_dt with my earlier code itself but when I try assign to a variable (Received_dt) it is breaking.

Re: Date variable is always converted to 01/01/1960 value.

ballardw — Tue, 18 Oct 2016 19:43:21 GMT

Try

file_received_dt=input(cats(day,mon,year),date9.);

Re: Date variable is always converted to 01/01/1960 value.

SASKiwi — Tue, 18 Oct 2016 19:45:25 GMT

Please supply some examples of the filenames you are trying to read.

Re: Date variable is always converted to 01/01/1960 value.

Kurt_Bremser — Wed, 19 Oct 2016 06:08:19 GMT

Look at the log at check for messages concerning automatic type conversion, missing values and the like.

Add put and %put statements at the crucial points in your code to verify that data step and macro variables contain exactly what you expected.

Be aware that a put in a data step with lots of iterations/observation might fatally swamp your log, so test first with a small sample set.

Re: Date variable is always converted to 01/01/1960 value.

jayakumarmm — Wed, 19 Oct 2016 12:47:39 GMT

Filenames are like "log response September 20 2016.xlsx"

Re: Date variable is always converted to 01/01/1960 value.

Kurt_Bremser — Wed, 19 Oct 2016 13:14:42 GMT

Before I'd go ANY farther with this, I'd make reasonable filenames mandatory. Anybody who uses a date format like that in an IT context is in dire need of being the target of a LART. And get rid of the stupid blanks, there's underlines for that.

As it is, I'd first do

filename = scan(filename,1,'.');

to remove the extension.

Then

year = input(scan(filename,-1,' '),4.);
day = input(scan(filename,-2,' '),2.);

For the month, I'd create a custom infomat that converts the textual month names to the numbers:

proc format;
invalue inmonth
  'January' = 1
  'February' = 2
....
  'September' = 9
...
;
run;

You can then use that in

month = input(scan(filename,-3,' '),inmonth.);

After that, use the mdy() function to build a date.

All that would not be necessary if some &?%!?# had used a yyyy-mm-dd format for the date in the filename.