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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

you only need to change the highlighted part?

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

HB
Barite | Level 11 HB
Barite | Level 11

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"

Linlin
Lapis Lazuli | Level 10

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

HB
Barite | Level 11 HB
Barite | Level 11

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.

Ksharp
Super User

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2553 views
  • 0 likes
  • 3 in conversation