BookmarkSubscribeRSS Feed
sarav93
Fluorite | Level 6

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

 

 

2 REPLIES 2
Kurt_Bremser
Super User

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
Meteorite | Level 14

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,

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 369 views
  • 2 likes
  • 3 in conversation