topic Re: Excel file import with year and value check and fix within the file in SAS Programming

Excel file import with year and value check and fix within the file

Emma2021 — Thu, 10 Jun 2021 12:59:41 GMT

I have excel file (attached). It has file name (Test 2020 file) and date variable with actual date such as 1/1/2020 etc.. Some year are incorrect (2045 and 1999), but all should be 2020 not 1999 or 2045. How can I check and fix accordingly? Thank you.

Re: Excel file import with year and value check and fix within the file

LeonidBatkhan — Thu, 10 Jun 2021 14:11:47 GMT

Hi Emma2021,

After you import your Excel file into SAS table, you can clean up your DATE variable. Let's say, you imported your Excel file into SAS dataset HAVE. Then you can do the following manipulation to reconstruct your DATE:

data WANT;
   set HAVE; 

   /* get day and month */
   D = day(DATE);
   M = month(DATE);

   /* reconstruct your DATE */
   DATE = mdy(M,D,2020);
run;

MDY() function calculates SAS date value out of Month, Day and Year.

Hope this helps.

Re: Excel file import with year and value check and fix within the file

Emma2021 — Thu, 10 Jun 2021 22:29:38 GMT

Instead of 2020 that hard code, there is a way to capture dynamically from the file name? Since there are 100 files--it would be hard to manually write like this.
For example files names are as the following:
Test 2000 Q1 file
Test another 2000 Q2
Test vk 2001 Q1 etc

Re: Excel file import with year and value check and fix within the file

jimbarbour — Thu, 10 Jun 2021 22:54:43 GMT

@Emma2021, how are you importing the files? Do you have a macro that searches a particular directory and processes all files of a certain type?

At some point in the processing, the file name must be known or you won't be able to process it. If we can capture the file name, we can extract the year and then add it to the MDY function mentioned in @LeonidBatkhan's reply.

Jim

Re: Excel file import with year and value check and fix within the file

jimbarbour — Thu, 10 Jun 2021 23:40:09 GMT

You of course have to first be able to determine the filename inside a Data step, but once you've determined the filename, a parsing routine like the one below can extract the year from the filename.

DATA	WORK.Years_Only;
	DROP	_:;
	LENGTH	_File_Name	$256;

	INFILE	DATALINES	MISSOVER;

	INPUT	_File_Name	$;
	
	_No_More_Characters							=	0;
	_Start_Pos									=	1;

	DO	UNTIL	(_No_More_Characters);
		_Start_Pos								=	ANYDIGIT(_File_Name, _Start_Pos);
		_End_Pos								=	_Start_Pos	+	3;

		IF	_Start_Pos							=	0					OR
			_Start_Pos							>	LENGTH(_File_Name)	OR
			_End_Pos							>	LENGTH(_File_Name)	THEN
			_No_More_Characters					=	1;
		ELSE
			IF	NOTDIGIT(SUBSTR(_File_Name, _Start_Pos, 4))	THEN
				DO;
					_Start_PoS					=	_Start_Pos	+	1;
					IF	_Start_Pos				>	LENGTH(_File_Name)	THEN
						_No_More_Characters		=	1;
				END;
			ELSE
				DO;
					Year						=	SUBSTR(_File_Name, _Start_Pos, 4);
					_No_More_Characters			=	1;
				END;
	END;

DATALINES;
Test_2000_Q1_file
Test_another_2000_Q2
Test_vk_2001_Q1
Test1_2002
Test26_2003_50
Test75_80_2004
No_Year_Here
2005_is_the_year
Howabout2006forayear
;
RUN;

Jim