SAS: Creating multiple datasets at once from an excel file

1
votes

I have an Excel file with 500 stock tickers. I wish to extract from a big a SAS dataset each stock's returns and additional observations. Before, say for 20 stock tickers, I would do the follow:

data s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11 s12 s13 s14 s15 s16 s17 s18 s19 s20;

set tick;
if stock='DIS' then output s1;
if stock='AA' then output s2; 
if stock='QCOM' then output s3; 
if stock='FB' then output s4; 
if stock='AMGN' then output s5;
if stock='AMZN' then output s6; 
if stock='AXP' then output s7; 
if stock='NWS' then output s8; 
if stock='INTC' then output s9; 
if stock='KRFT' then output s10;
if stock='CB' then output s11; 
if stock='CELG' then output s12; 
if stock='CMCSA' then output s13; 
if stock='COST' then  output s14; 
if stock='CSCO' then output s15;
if stock='YHOO' then output s16; 
if stock='DELL' then output s17; 
if stock='VOD' then output s18; 
if stock='DOW' then output s19; 
if stock='EBAY' then output s20;
run;

where the tick SAS dataset contains the entire population of stock returns.

Then for each s1, s2....s20 I use a loop to iterate among the 20 different files and apply some additional SAS code.

I want to avoid filling up my SAS code with 500 lines of e.g. if stock='COST' then output s14; if I need to apply a series of SAS code on each ticker.

Is there a way for me to have SAS loop that goes through each line of my excel file, say it selects the first ticker, creates a SAS dataset s1 then I apply some SAS code to this s1 file and once this is done, go back to the top of the loop, select the second line of my excel (hence the second ticker) and repeat the process?

2
loopssas

2 Answers

2
votes

First, you are probably better off leaving things in one data set and using by groups to do down stream processing.

If you must, you can script this with a macro like this:

%macro split();
proc sql noprint;
select count(distinct stock)
    into :n
    from tick;

select distinct stock
    into :s1 - :s%left(&n)
    from tick;
quit;

data
    %do i=1 %to &n;
    s&i
    %end;
;
set tick;

%do i=1 %to &n;
   if stock = "&&s&i" then output s&i;
%end;
run;
%mend;

%split();
2
votes

The best solution, as Dom says, is to do this with one dataset and BY groups.

The second best solution is to code your already existing macro like so:

%macro dostuff(stock=);
data mystuff/view=mystuff;
set tick;
where stock="&stock.";
run;

... do stuff ...
%mend;

That creates a view that you then use for your downstream processing without having to create a physical dataset - and it allows you to create it on demand, rather than pre-creating the dataset. If you properly index the tick dataset on stock, it should be as fast or faster than creating multiple datasets, even though it is a multiple pass solution (since it's a view).

-Joe