Help using Base SAS procedures

file name with a variable in a loop

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 162
Accepted Solution

file name with a variable in a loop

Moving along.

I have this block of code that generates a nice Excel listing file.

LIBNAME MYXLS2 "K:\place_where_files_go\hb\entity.xls";

PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;

proc sql;
create table myxls2.data as
  select fieldname1, fieldname2, fieldname3, fieldname4
  from table
  where fieldname5='entity';

This gets repeated 44 times- one time for each entity for which an output file is generated.  There are 44 entities.

What I would like is something like:

 

list = some sort of list (array?) of entities

for each entity in list

LIBNAME MYXLS2 "K:\place_where_files_go\hb\<a variable that changes each time through the loop and is the entity>.xls";

PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;

proc sql;
create table myxls2.data as
  select fieldname1, fieldname2, fieldname3, fieldname4
  from table
  where fieldname5='<the entity variable again>';

next entity

so that I don't have to repeat the code block a gazillion times.

Is such a thing possible?


Accepted Solutions
Solution
‎03-29-2012 06:47 PM
Super Contributor
Posts: 1,636

Re: file name with a variable in a loop

Thy this one. (not tested)

%let names=fred jane george;

%macro test;

%do i=1 %to %sysfunc(countw(&names));

%let var=%sysfunc(scan(&names,&i));

LIBNAME MYXLS2 "K:\place_where_files_go\hb\&var..xls";

PROC DATASETS LIB=MYXLS2;

DELETE 'data'n;

RUN;

QUIT;

proc sql;

create table myxls2.data as

  select fieldname1, fieldname2, fieldname3, fieldname4

  from table  ???

  where upcase(fieldname5)="%upcase(&var)";

quit;

libname MYXLS2 clear;

%end;

%mend;

%test

View solution in original post


All Replies
Super Contributor
Posts: 1,636

file name with a variable in a loop

you only need to change the highlighted part?

where fieldname5='<the entity variable again>';

Regular Contributor
Regular Contributor
Posts: 162

file name with a variable in a loop

No the code block generating the excel file would need to change in 2 places.

One- the name of the file outputted.  First time through the loop it might be fred.xls, second time through jane.xls, third time through george.xls   

K:\place_where_files_go\hb\<a variable that changes each time through the loop and is the entity>.xls";

Two the SQL woudl change. Where fieldname5="fred", Where fieldname5="jane", Where fieldname5="george" and so on

I was thinking this might somehow work from a listing.

names="fred,jane,george"

Solution
‎03-29-2012 06:47 PM
Super Contributor
Posts: 1,636

Re: file name with a variable in a loop

Thy this one. (not tested)

%let names=fred jane george;

%macro test;

%do i=1 %to %sysfunc(countw(&names));

%let var=%sysfunc(scan(&names,&i));

LIBNAME MYXLS2 "K:\place_where_files_go\hb\&var..xls";

PROC DATASETS LIB=MYXLS2;

DELETE 'data'n;

RUN;

QUIT;

proc sql;

create table myxls2.data as

  select fieldname1, fieldname2, fieldname3, fieldname4

  from table  ???

  where upcase(fieldname5)="%upcase(&var)";

quit;

libname MYXLS2 clear;

%end;

%mend;

%test

Regular Contributor
Regular Contributor
Posts: 162

Re: file name with a variable in a loop

Linlin,

If that is your untested code, I don't want to be around when you break out the weapons grade fully tested stuff.

That worked first time first run out of the box.

I did this:

data have;
input junkname $;
datalines;
jane
bob
danny
fred
wilbur
george
jorge
;

%let names=fred jane george;

%macro test;
%do i=1 %to %sysfunc(countw(&names));
%let var=%sysfunc(scan(&names,&i));

LIBNAME MYXLS2 "K:\fileplace\&var..xls";
PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;

proc sql;
create table myxls2.data as
  select junkname
  from have
  where upcase(junkname)="%upcase(&var)";
quit;

libname MYXLS2 clear;

%end;
%mend;
%test

Created 3 xls files off of the SQL slicker than some new military substance engineered to be super slick.

Thanks.

Super User
Posts: 10,046

Re: file name with a variable in a loop

Can you use PIPE function at SAS?

The following code can get the excel file name you want.

filename x PIPE 'dir K:\place_where_files_go\hb\*.xls /b' ;

And libname statement + dictionary can get very sheets name in every excel file .

Ksharp

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 531 views
  • 0 likes
  • 3 in conversation