DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

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

[ Edited ]

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 #

        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;

Accepted Solutions
Solution
‎10-19-2016 10:17 AM
Super User
Posts: 6,928

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 3,101

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

Try this:

 

file_received_dt= mdy(mon,day,year);
Contributor
Posts: 55

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

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.
Super User
Posts: 10,466

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

Try

 

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

Super User
Posts: 3,101

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

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

Contributor
Posts: 55

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

Filenames are like "log response September 20 2016.xlsx"
Solution
‎10-19-2016 10:17 AM
Super User
Posts: 6,928

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 441 views
  • 0 likes
  • 4 in conversation