topic Proc import : Capture the date as a new column in SAS Programming

Proc import : Capture the date as a new column

sarav93 — Tue, 05 May 2020 13:51:28 GMT

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

Re: Proc import : Capture the date as a new column

Kurt_Bremser — Tue, 05 May 2020 14:15:17 GMT

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;

Re: Proc import : Capture the date as a new column

ed_sas_member — Tue, 05 May 2020 14:24:37 GMT

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,