BookmarkSubscribeRSS Feed
LilyVyhanek
Calcite | Level 5

Hi Experts! 

 

I am trying to write a code that imports weekly excel files into one large table (this table should encapsulate all the weekly excel tables to create one large monthly table), the issue is that the days that they are reported are contantly changing so it can't be a hard code that can be repeated monthly. My solution to this was for the user to just input the dates that are given.  Here is what i have so far: 

 

%Let Date1 = 2023-8-2;
%Let Date2 = 2023-8-9;
%Let Date3 = 2023-8-16;
%Let Date4 = 2023-8-23;
%Let Date5 = 2023-8-30;

 

%Let FilePath2 = \\path\to\my\Data\SFC Funding Notification &Date1..xlsx;
LIBNAME ClMed2 xlsx "&FilePath2.";

proc contents data=ClMed2._all_ out=SheetNames(keep=memname) noprint;
run;
proc sort data=SheetNames;
by memname;
run;
data _null_;
set SheetNames end=last;
if last then call symputx('LastSheet', memname);
run;
data ClMed2_LastSheet;
set ClMed2."&LastSheet"n(firstobs=3);
run;

 

(The reason why the import is odd is because the name of the excel tabs is also constantly changing, but it always the last sheet so this was my attempt at only importing the last sheet on the excels )

What would be the best way to loop this so that the data table populates with the tables called "SFC Funding Notification 2023-8-2", "SFC Funding Notification 2023-8-9",  "SFC Funding Notification 2023-8-16", ... etc. 

1 REPLY 1
ballardw
Super User

Once you have a library pointing to the spreadsheet then easiest would be to have another library define and copy of all of the sheets to the library.

I would set OPTIONs VALIDMEMNAME=COMPATIBLE VALIDVARNAME=V7;  before assigning that library to the spreadsheet so the non-standard names don't make more problems coding later. That can get around having to use those name literals for example.

 

Also you will not get those target names. They are too long. SAS data set names are limited to 32 characters and those names are 34 (or 35 with a 2 digit month). Which may be another problem because of the name truncation will yield duplicate names.

I would at least try this one time to see what you get.

libname mylocal "<path>\myfolder";
options validmemname=compatible validvarname=v7;
LIBNAME ClMed2 xlsx "&FilePath2."; /* personally for the first trial there is no reason to use a macro variable*/ proc copy in=ClMed2 out=mylocal memtype=data; run;

If this works then I would use a data step to write the code to a program file instead of trying to create multiple date variables and loop over them but get one working first.

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1 reply
  • 511 views
  • 0 likes
  • 2 in conversation