topic How to run the same procedure on all datasets within a lib.? in SAS Programming

How to run the same procedure on all datasets within a lib.?

igsteo — Thu, 21 Jan 2021 08:35:47 GMT

I am new to SAS and greatly appreciate some help.

I have imported an excel file containing multiple worksheets; a dataset has been created based on each individual worksheet.

How should i loop the codes below for all the datasets within this library 'hsefile'?

"STI_Index__pctchng=dif(STI_Index__Close_)/lag(STI_Index__Close_);
label
STI_Index__pctchng='STI Index Returns';"

and

Is it possible to do a proc autoreg for all datasets as well?

"proc autoreg data=
model Reit_rp = Mkt_rp / dwprob;
test Mkt_rp = 1;
output out=reitout p=p r=r ucl=u lcl=l alphacli=.10;
title2;
title3;"

Re: How to run the same procedure on all datasets within a lib.?

Kurt_Bremser — Thu, 21 Jan 2021 08:51:12 GMT

Combine the datasets into one with a view.

If your different datasets constitute separate groups, do this in a data step view with the INDSNAME= option in the SET statement; you can then use BY group processing in a single procedure call.

proc sql noprint;
select memname into :datasets separated by " "
from dictionary.tables
where libname = "XXX";
quit;

data all / view=all;
length ds dsname $41;
set &datasets. indsname=ds;
dsname = ds;
run;

proc autoreg data=all;
by dsname;
/* further code */

Re: How to run the same procedure on all datasets within a lib.?

igsteo — Fri, 22 Jan 2021 03:00:04 GMT

I want to combine all datasets in the library. Would this mean my code is as follows for proc sql?

I tried it but it doesn't seem to capture. or do i have to enter every single name after 'select'?

appreciate the help rendered!

proc sql noprint; select memname into :datasets separated by " " from dictionary.tables where libname = "hsefile"; quit;

Re: How to run the same procedure on all datasets within a lib.?

SASKiwi — Fri, 22 Jan 2021 03:04:33 GMT

Try changing your LIBNAME selection to uppercase:

where libname = "HSEFILE";

Re: How to run the same procedure on all datasets within a lib.?

igsteo — Fri, 22 Jan 2021 05:56:30 GMT

PROC CONTENTS DATA=hsefile._ALL_ ; 
RUN;
proc sql;
select memname into :datasets separated by " "
from dictionary.tables
where libname = "HSEFILE";
quit;
%macro want;
%do i=1 %to 36;
Index__pctchng=dif(Index__Close_)/lag(Index__Close_);
%end;
%want;

Would a macros work to perform the same calculation on all members in the list created using procsql? What's the alternate codes if this should not function?

each member has the stock ticker ending with a number i.e. TSLA_1, AAPL_2.

Hence, i'm trying to specify calculations to be ran from e.g. 1 to 36.

Re: How to run the same procedure on all datasets within a lib.?

Kurt_Bremser — Fri, 22 Jan 2021 15:08:52 GMT

When working with the dictionary tables, library and library member (dataset, view, catalog) names must always be specified in uppercase.

Edit: the above is true for SAS libraries; databases used via SAS/ACCESS may behave differently (see @andreas_lds's post).

Re: How to run the same procedure on all datasets within a lib.?

andreas_lds — Fri, 22 Jan 2021 09:29:24 GMT

@Kurt_Bremser wrote:

When working with the dictionary tables, library and library member (dataset, view, catalog) names must always be specified in uppercase.

Unfortunately some exceptions exist when using MySQL-tables:

the variable memname in sashelp.vtable and sashelp.vcolum seems to be all lower-case.
the variable name in sashelp.vcolumn is mixed-case, matching the case that is used in the database
all this may depend on how the database is accessed

Re: How to run the same procedure on all datasets within a lib.?

igsteo — Mon, 25 Jan 2021 02:12:31 GMT

data all / view=all;
length ds dsname $41;
set &datasets. indsname=ds;
dsname = ds;
run;

I seem to be getting an error on the third line. As i'm new to SAS, am i supposed to change &datasets to all 40 datasets that i have? or can i just specify the library name?

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS,
END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 76-322: Syntax error, statement will be ignored.

WARNING: Apparent symbolic reference DATASETS not resolved.
ERROR 22-322: Syntax error, expecting one of the following: CUROBS, END, INDSNAME, KEY, KEYRESET,
KEYS, NOBS, POINT.

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: How to run the same procedure on all datasets within a lib.?

Kurt_Bremser — Mon, 25 Jan 2021 02:33:03 GMT

Macro variable datasets has not been set. Check if the SQL step can find any datasets.

Re: How to run the same procedure on all datasets within a lib.?

igsteo — Mon, 25 Jan 2021 02:53:11 GMT

i did print the proc sql and it reflects all the members in the library that was specified.

Re: How to run the same procedure on all datasets within a lib.?

Kurt_Bremser — Mon, 25 Jan 2021 03:05:38 GMT

The log is very clear, macro variable datasets has not been defined.

Run both steps in one submit, and post the complete log. Use the </> button for posting the log, DO NOT SKIP THIS.

Re: How to run the same procedure on all datasets within a lib.?

igsteo — Mon, 25 Jan 2021 08:48:44 GMT

Thank you for all the help i have combined all datasets into a table. However, as you can see only WORK.FCT appears to have created a separate 'adjusted close' column when it has the same variable as the other datasets. I have checked my excel file and all layout is uniform. How could i solve this?

Re: How to run the same procedure on all datasets within a lib.?

Kurt_Bremser — Mon, 25 Jan 2021 08:42:08 GMT

So your real issue is the use of manually entered (therefore the typo in the column name) data in a mostly unusable file format (Excel) which forces you to clean the data first before working with it.

If you want to do any successful automation here, you have to insist that the data be sent in a usable file format (csv or fixed column width) where you can consistently set variable attributes in the data step that reads the data into SAS.

Re: How to run the same procedure on all datasets within a lib.?

igsteo — Mon, 25 Jan 2021 08:49:08 GMT

Thank you for all the help!