topic Re: file name with a variable in a loop in SAS Procedures

file name with a variable in a loop

HB — Thu, 29 Mar 2012 20:09:59 GMT

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?

file name with a variable in a loop

Linlin — Thu, 29 Mar 2012 20:26:27 GMT

you only need to change the highlighted part?

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

file name with a variable in a loop

HB — Thu, 29 Mar 2012 21:00:05 GMT

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"

Re: file name with a variable in a loop

Linlin — Thu, 29 Mar 2012 22:47:34 GMT

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

Re: file name with a variable in a loop

Ksharp — Fri, 30 Mar 2012 04:03:51 GMT

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

Re: file name with a variable in a loop

HB — Fri, 30 Mar 2012 14:39:12 GMT

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.