Proc import : Capture the date as a new column

[sarav93]

Good Evening to all..

I am having a file "xxxx_20200101.xlsx" . This file contains prefix of xxx and with the date of file extraction as 20200101. i am using proc import to read this file and creating a new data set. 

While i am creating a new data set i need to capture this date(20200101) in an new column.

ex:

Col_1   Col_2       Col_3        File_Date

01        xxxx          aaaa       20200101

02        yyyy          bbbb       20200101

03        zzzz          cccc        20200101

Thanks in advance..

[Kurt_Bremser]

Preferred: save the data to a csv (comma-separated) file from Excel, and use a data step to read it:

%let infile_date = 20200101;

data want;
infile "/..../xxx_&infile_date..csv";
input col_1 :$2. col_2 :$4. col_3 :$4.;
file_date = input("&infile_date",yymmdd8.);
format file_date yymmddd10.;
run;

or, if the unreliable Excel format is unescapable, set the additional variable in a follow-up data step:

%let infile_date = 20200101;

proc import
  datafile="/..../xxx_&infile_date..xlsx"
  out=import
  dbms=xlsx
  replace
;
run;

data want;
set import;
file_date = input("&infile_date",yymmdd8.);
format file_date yymmddd10.;
run;

[ed_sas_member]

Hi @sarav93 

I am thinking about something like this (untested code)

%let myfile = "xxxx_20200101.xlsx";

proc import datafile=&myfile.
		    out=want
		    dbms=xlsx
		    replace;
run;

data want2;
	set want;
 	format File_Date YYMMDDn8.;
	File_Date = input(substr(&myfile,length(&myfile.)-12,8),YYMMDD8.);
run;

Best,