topic Re: Writing a loop to read data name in a macro in SAS Programming

Writing a loop to read data name in a macro

Fred_Gavin — Wed, 11 Apr 2012 05:50:34 GMT

I wrote a macro that can recursively input the similar datasets into one data file.

It will be no problem for me to change the data name one by one if there are only several datasets.

However now, there are more than 100 datasets. How can I write a macro loop to read data names automatically?

For example:

%getdata(dataname) is the macro I wrote.

What I want to do is to

pesudecode:

%do name = A, B, C, D, E, F, G, ......

%getdata(name)

%end;

Thanks for the help in advance.

Re: Writing a loop to read data name in a macro

manojinpec — Wed, 11 Apr 2012 06:50:26 GMT

Try below.i haven't tested the program.

%let name=A,B,C,D,E.. ;

%let dataset=%scan(&name,1,',');

%do %while(&dataset ne '');

%let dataset=%scan(&name,&i,',');

%let i=%eval(&i+1);

%end;

Re: Writing a loop to read data name in a macro

Ksharp — Wed, 11 Apr 2012 06:54:15 GMT

Easy. Assuming all of your datasets are under C:\

libname x v9 'c:\';
data _null_;
 set sashelp.vmember(keep=libname memname where=(libname='X'));
 call execute('%getdata('||memname||')');
run;

Ksharp

Re: Writing a loop to read data name in a macro

Fred_Gavin — Wed, 11 Apr 2012 07:01:32 GMT

Sorry, forgot to mention that. The datasets do not exists. They are the stock prices to be downloaded from Yahoo Finance. And A, B, C, D, E... are just stock codes.

%getdata(dataname), is to perform the download first, and recursively combine the data downloaded together in one file.

That's why I need to find a way to read the 100 stock codes automatically, rather than change them manually.

Thank you

Re: Writing a loop to read data name in a macro

manojinpec — Wed, 11 Apr 2012 07:15:23 GMT

what is the connection you use between yahoo finance and sas?

Re: Writing a loop to read data name in a macro

Fred_Gavin — Wed, 11 Apr 2012 07:18:20 GMT

FILENAME myurl URL "http://ichart.finance.yahoo.com/table.csv?s=&tic..AX";

it's for australian stocks, so i put AX there. otherwise, you can leave it as ...=&tic"

Re: Writing a loop to read data name in a macro

Ksharp — Wed, 11 Apr 2012 08:17:24 GMT

If you already have a list of stock code. It is also easy.

data x;
input stock $;
cards;
A
B
C
D
;
run;

data _null_;
 set x;
 call execute('%getdata('||stock||')');
run;

Ksharp

Re: Writing a loop to read data name in a macro

manojinpec — Wed, 11 Apr 2012 09:38:48 GMT

Hi,

This is new thing for me. Which sas utility is requried to fecth data from website.

Re: Writing a loop to read data name in a macro

Fred_Gavin — Wed, 11 Apr 2012 10:40:22 GMT

It worked for the stock separately, which means the final data file only contains one stock only.

But I want the final data file containing all stocks fetched from Yahoo.

If I run %getdata(MCE) first, then it will generate a file called 'ALL', which contains MCE stock prices.

then run%getdata(BSL), then BSL data will be appended below MCE in 'ALL' recursively, and so on.

I dont know which part is not quite right.

Thanks

Re: Writing a loop to read data name in a macro

Fred_Gavin — Wed, 11 Apr 2012 11:47:45 GMT

I think this code is useful, and I may redesign my own program and embed this to make it work.

Thank you very much.

Re: Writing a loop to read data name in a macro

Fred_Gavin — Wed, 11 Apr 2012 11:50:53 GMT

Nth is new, still the data step with one additional line with URL.

You can search Google that "download stock from Yahoo Finance in SAS", then you can find how it was done.

Re: Writing a loop to read data name in a macro

Ksharp — Thu, 12 Apr 2012 06:12:33 GMT

You can add another proc append after every macro be done.

data x;

input stock $;

cards;

;

run;

data _null_;

set x;

call execute('%getdata('||stock||')');

call execute('proc append base=want data=all force;run;');

run;

Ksharp