BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
philfoot123
Calcite | Level 5

Hey there,

So I've been spinning my wheels at this for way too long that I feel like there must be a better way.

I have an excel workbook that I have assigned a libname and I would like to do formatting on the sheets which are labeled in a way that I thought would be conducive to set up a loop. Unfortunately the fact that the sheets are named with single quotes around them makes referencing a macro variable difficult.  This is my closest attempt but the quotes get lost in the call symputx statement.

Any help would be greatly appreciated

libname &facil_export. "&path.\&facil_export._clean.xlsx";

%macro import_loop;

%do i = 1 %to &periods.;

%let sheet = "Period (&i.)$";

%let the_n = n;

data _null_;

format sheet_v2 $15.;

sheet_v2 = tranwrd(&sheet.,'"',"'");

call symputx('sheet_final', sheet_v2);

run;

data &facil_export._&i.; set &facil_export..&sheet_final.&the_n.;

run;

%end;

%mend;

%import_loop;

1 ACCEPTED SOLUTION

Accepted Solutions
philfoot123
Calcite | Level 5

Thanks guys - I ended up going a different route and utilizing the sashelp.vstabvw table to create an index of the sheet names.  This paper outlines the technique - I appreciate the feedback though !

http://www2.sas.com/proceedings/sugi31/034-31.pdf

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

I do not understand the problem.  What SAS code are you trying to generate?

Note that name literals (and other SAS literals) can use double quotes or single quotes.


%let i=5 ;

%put "Period (&i)$"n;

Note that you can use single quotes around macro variables if they are not the outer quotes.

%let i=5 ;

%put "'Period (&i)$'"n;


philfoot123
Calcite | Level 5

Hey Tom,

The issue I'm having is that, using the libname engine - a excel sheet needs to have the single quotes around it to be referenced and since i'm trying to reference many sheets i'm using a macro loop.  I haven't been able to figure out how I can output the sheet name (ie - 'Period (1)'n Period (2)'n etc.) with the macro do-loop variable.

I tried both of your examples above but with no luck.

Tom
Super User Tom
Super User

First get it to work typing them by hand. 

If the code you posted generates the string you want, but it does not work in might be a macro quoting issue.  Try either generating the concatenated text to a new macro variable or wrapping it in a %unquote() function.

set %unquote(&facil_export..&sheet_final.&the_n.) ;

Tom
Super User Tom
Super User

Are you sure of your sheet names?

8     options validvarname=any;

9

10    %let i=1;

11    data x&i;

12     set xx."'Part (&i)$'"n ;

13     run;

NOTE: There were 31 observations read from the data set XX."'Part (1)$'"n.

NOTE: The data set WORK.X1 has 31 observations and 4 variables.

SASKiwi
PROC Star

Have a look at my example that uses dictionary tables and PROC DATASETS rather than macro looping. It does rely on the Excel tabs conforming to SAS dataset naming conventions though:

https://communities.sas.com/message/170166#170166

philfoot123
Calcite | Level 5

Thanks guys - I ended up going a different route and utilizing the sashelp.vstabvw table to create an index of the sheet names.  This paper outlines the technique - I appreciate the feedback though !

http://www2.sas.com/proceedings/sugi31/034-31.pdf

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 choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 5506 views
  • 6 likes
  • 3 in conversation