BookmarkSubscribeRSS Feed
hernansj
Fluorite | Level 6

Hello,

 

I am trying to PROC IMPORT several files in a loop. Some of the files have the name SP15_[Year] and others have SP15_[Year]_[Year+1]. Seems like I should be able to use %DO %WHILE for this because there exists a constant, c such that for [Year] > c, the files have two years in the name.

 

In my %DO %WHILE loop I keep getting errors related to quoted strings. But I can use CTRL+F to verify that all quotations are closed. If I take one of the PROC IMPORT commands out of the loop and simply force the loop index to take a particular value, I get the 'ERROR: Macro keyword LET appears as text' message.

 

The result I want is a set of SAS datasets named the following (i.e. the SAS dataset name can/should be the same as the imported file name):

 

SP15_2017
SP15_2018
SP15_2019_2020
SP15_2021_2022
SP15_2023_2024
SP15_2025_2026
SP15_2027_2028
SP15_2029_2030 

 

 

My simplified attempt is:

*****************************

%let i = 2017;
proc import datafile= "&path/SP15_&i" out=SP15_&i dbms=xlsx replace;
RANGE="Sheet 1$.A1:I8761";
GETNAMES=YES;
run;

(This returns the ERROR in the subject)

*****************************

My %DO %WHILE loop is:

*****************************


%macro extract(startyear, endyear);
%do i = &startyear %to &endyear;
%do %while(&i in (2017, 2018));

proc import datafile= "&path/SP15_&i" out=SP15_&i dbms=xlsx replace;
RANGE="Sheet 1$.A1:I8761";
GETNAMES=YES;
run;

%end;
%do %while(&i in (2019, 2020));

proc import datafile= "&path/SP15_2019_2020" out=SP15_2019_2020 dbms=xlsx replace;
RANGE="Sheet 1$.A1:I8761";
GETNAMES=YES;
run;

%end;
%do %while(&i in (2020, 2028));

%let k = %eval(&i +1);
%let j = %eval(&k + 1);

proc import datafile= "&path/SP15_&k_&j" out=SP15_&k_&j dbms=xlsx replace;
RANGE="Sheet 1$.A1:I8761";
GETNAMES=YES;
run;

%end;

%do %while(&i = 2029);
proc import datafile= "&path/SP15_2029_2030" out=SP15_2029_2030 dbms=xlsx replace;
RANGE="Sheet 1$.A1:I8761";
GETNAMES=YES;
run;
%end;

%end;
%mend;

%extract(2017, 2030);

(This returns "The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succceeding identifier is recommended." This error is returned at the top and the bottom of the log. The bottom of the log also returns "The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks.")

 

Thanks for your suggestions.

-S

 

5 REPLIES 5
Reeza
Super User

How about using SAS to pull the name of the files automatically using your standard windows/file list options and then automate your import that way. 

 

Here's a macro to import all XLSX files in a directory, but you would need to modify it to handle the Sheet/Range I see in yours. 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

 

Comments on your code:

 

I don't think this does what you expect, it checks for only the values 2020 and 2028 (not 2021, 2022 etc) and I'm not even sure it's the correct syntax since it should be macro code. 

%do %while(&i in (2020, 2028));

The text issue error is probably left over for something else. I suspect if you start a new SAS session and submit the code shown you will not have issues. Make sure if you do include the asterisks line in your code that there's a semicolon at the end, otherwise it's interpreted as a comment, see below which is a comment. 

 

*************
%let i=1;

 

art297
Opal | Level 21

I agree with @Reeza's comments and suggestions.

 

However I did want to add that your %do %while loops are infinite loops. You start with a do loop initially setting the value of &i to be 2017, then use:

 

%do %while(&i in (2017, 2018));
  proc import datafile= "&path/SP15_&i" out=SP15_&i dbms=xlsx replace;
    RANGE="Sheet 1$.A1:I8761";
    GETNAMES=YES;
  run;
%end;

Since nothing changes the value of &i during that process, the code would keep importing and outputing the same Workbook until you manually killed the process.

 

It would make more sense to use:

%if &i in (2017, 2018) %then;
  proc import datafile= "&path/SP15_&i" out=SP15_&i dbms=xlsx replace;
    RANGE="Sheet 1$.A1:I8761";
    GETNAMES=YES;
  run;
%end;

Art, CEO, AnalystFinder.com

 

hernansj
Fluorite | Level 6

 

Thank you for the suggestions. I was able to run the import code you suggested successfully. The only problem is that the output datasets are named TEST## instead of the original file names. Can you suggest how to modify? Would it be the data _null_ command at the end?

 

-S

Tom
Super User Tom
Super User

You can simplify your looping a lot by just using a normal iterative %DO loop and some testing for the special cases.

%macro test;
%local i name;
%do i=2017 %to 2029 ;
  %if %sysfunc(mod(&i,2)) or &i=2018 %then %do;
    %let name=SP15_&i ;
    %if &i > 2018 %then %let name=&name._%eval(&i+1);
    %put &=i &=name ;
  %end;
%end;
%mend ;
%test ;

Just replace the %PUT statement with the code you want to run.

hernansj
Fluorite | Level 6

Tom,

 

Thanks for suggesting such a simple solution. I'm going with this. It's great to use as few lines as possible.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 5 replies
  • 1786 views
  • 5 likes
  • 4 in conversation