Error in data set firstobs

问题:

I am very, very new to SAS and have been reading stackoverflow questions and SAS documentation, to write code for a very specific purpose. I have been having a hard time achieving my goal and understanding SAS for two reasons: I can only execute code on a remote server though SSH because I do not have SAS locally (so, on every change, I need to upload the file, execute and get the log and lst files back, check for errors) and most of the introductory topics I have read about are not immediately applicable for my task (I am only interested in using SAS to automate a certain data extraction procedure).

My goal is to:

• read certain tickers (i.e. an identifier for a stock) on a CSV file;
• loop each ticker, retrieving the information I need through certain macros.

So far, I've succeeded in reading the .csv and importing that data to a dataset. To test whether the basics of what I need are working correctly, I made the following code. My goal was, through a loop, to assign the ticker to a certain "variable" currentticker (probably not the right name for it) and print it. The csv file only has two lines it says "IBM" in the first and "DELL" in the other.

libname mydir '~/';

data companies;
  infile 'sastests/data/tickers.csv' delimiter=',';
  input ticker $;
run;

proc sql;
 select    count(*)
 into      :OBSCOUNT
 from      companies;
quit;

proc print data=companies;
  var ticker;
run;

%do iter = 1 to &OBSCOUNT;
  data currentticker;
    set companies (firstobs = iter obs = iter);
  run;
  proc print data = currentticker;
  run;
%end;

When I go through the log file, I get an error immediately in the firstobs option of data set.

Invalid value for the FIRSTOBS option.

Why is this so? Shouldn't iter be a number, thus being valid as a FIRSTOBS?

Thank you very much in advance.

Edit 1: Title was not a good description of the problem.

Edit 2: Examples of the macros to be used for a single ticker. Lookup would have to be feeded with &ticker. lookup would be called, then getopt and finally export_tab. This code is not of my authorship, I modified it slightly after it was provided as sample code by WRDS.

%macro lookup;

  data idcodes (keep=secid);
  set optionm.secnmd;
  where lowcase(ticker) = &ticker;

  proc sort data=idcodes nodupkey;
    by secid;

  proc print data=idcodes;

%mend;

%macro getopt(year);

  proc sql;
    create table temp as
      select a.* 
      from
        optionm.vsurfd&year as a,
        idcodes as b
      where
        a.secid = b.secid;
  run;

  proc datasets;
    append base=work.&outputfile
    data=work.temp;
  run;

%mend;

%macro export_tab;

  proc export data=&outputfile outfile="&outputfile._out.txt" dbms=tab replace;
  run;

%mend;

回答1:

This is basically another answer, so putting it here. This is how I'd approach the second part - no macros at all . I'm assuming their yearly datasets are sorted by secid already; if not, this might be a bit more complicated, just to avoid the merge then.

proc sql;
select quote(ticker) into :tickerlist separated by ',' from companies;
quit;

data idcodes;  *you could also create this by merging optiomn.secnmd to companies by ticker.;
set optionm.secnmd;
where lowcase(ticker) in (&tickers.);
run;

proc sort data=idcodes nodupkey;
by secid;
run;

proc print data=idcodes;
run;

data lotsofyears/view=lotsofyears;
set
optionm.vsurfd2010
optionm.vsurfd2011
optionm.vsurfd2012
optionm.vsurfd2013
;  *or however many you need, you could generate this list if it is long;
by secid;
run;

data mydata;
merge lotsofyears(in=a) idcodes(in=b);
by secid;
filenm=cats("c:\mydir\mydata_",ticker,".dat"); *or secid if that is better;
run;
proc sort data=mydata;
by ticker;
run;
data _null_;
set mydata;
file a filevar=filenm dlm='09'x lrecl=32767;
put (_all_)($); *or perhaps a more complex put statement - see what proc export generates;
run;

回答2:

You need to precede the macro variable by &, so firstobs=&iter..

However, i'm not sure you really want to do this. Almost anything you might want to do by macro-iterating over the dataset you can do by regular-iterating over the dataset, and perhaps using BY groups. Macro iteration is highly inefficient compared to using built-in SAS techniques.

To call a set of code for each line in a dataset, you would do something like this.

%macro pulldata(ticker);
data stock_data;
set big_database;
where ticker="&ticker";
file "c:\mydir\myfile_&ticker..csv" dlm=',' lrecl=32767; *double period - first is macro variable delimiter;
put (_all_) ($);
run;
%mend pulldata;

data _null_;
set companies;
call execute('%pulldata(',ticker,')');
run;

You also can do something like this, where we use the filevar option to allow a dataset to be put out to multiple files (must be sorted by filevar!)

proc sql;
select quote(ticker) into :tickerlist separated by ',' from companies;
quit;

data mydata;
set bigdata;
where ticker in (&tickerlist.);
filen = cats('c:\mydata\myfile_',ticker,'.csv');
run;

proc sort data=mydata;
by filen;
run;

data _null_;
set mydata;
file a filevar=filen dlm=',' lrecl=32767;
put (_all_) ($);
run;