CODEWITHSUNDEEP
loopssas
phdstudent
phdstudent

Reputation: 1144

Loop through list

I have the following macro:

rsubmit;


data indexsecid;
input secid 1-6;
datalines;
108105
109764
102456
102480
101499
102434
107880
run;
%let endyear = 2014;

%macro getvols1;
  * First I extract the secids for all the options given a date and
  an expiry date;
  %do yearnum = 1996 %to &endyear;
  proc sql;
        create table volsurface1&yearnum as
        select a.secid, a.date, a.days, a.delta, a.impl_volatility,
        a.impl_strike, a.cp_flag
        from optionm.vsurfd&yearnum as a, indexsecid as b
        where a.secid=b 
        and a.impl_strike NE -99.99
        order by a.date, a.secid, a.impl_strike;
  quit;
%if &yearnum > 1996 %then %do;
proc append base= volsurface11996 data=volsurface1&yearnum;
run;
%end;
%end;



%mend;
%getvols1;


proc download data=volsurface11996;
run;    
endrsubmit;

data _null_;
set work.volsurface11996;
length fv $ 200;
fv = "C:\Users\user\Desktop\" || TRIM(put(indexsecid,4.)) || ".csv";
file write filevar=fv dsd dlm=',' lrecl=32000 ;
put (_all_) (:);
run;

On the code above I have: where a.secid=108105. Now I have a list with several secid and I need to run the macro once for each secid. I am looking to run it once and generate a new dataset for each secid. How can I do that? Thanks

Upvotes: 0

Views: 446

Answers (2)

phdstudent
phdstudent

Reputation: 1144

SRSwift thank you for your comprehensive answer. It run smoothly with no errors. The only issue is that I am running it on a remote server (wharton) using: 

 %let wrds=wrds.wharton.upenn.edu 4016;
 options comamid=TCP remote=wrds;
 signon username=_prompt_;
 rsubmit;

and on the log it says it wrote the file to my folder on the server but I can t see any file on the server. The log says:

NOTE: The file WRITE is:
      Filename=/home/uni/user/108505.csv,
      Owner Name=user,Group Name=uni,
      Access Permission=rw-r--r--,
      Last Modified=Wed Apr  1 20:11:20 2015

Upvotes: 0

SRSwift
SRSwift

Reputation: 1710

Here is an approach that uses

  • A single data step set statement to combine all the input datasets
  • A data set list so you don't have to call each input by name
  • A hash table to limit the output to your list of secids
  • proc sort to order the output
  • Rezza/DWal's approach to output separate csvs with file filevar =
%let startyear = 1996;
%let endyear = 2014;

data volsurface1;
  /* Read in all the input tables */
  set optionm.vsurfd&startyear.-optionm.vsurfd&endyear.;
  where impl_strike ~= -99.99;
  /* Set up a hash table containing all the wanted secids */
  if _N_ = 1 then do;
    declare hash h(dataset: "indexsecid");
    _rc = h.defineKey("secid");
    _rc = h.defineDone();
  end;
  /* Only keep observations where secid is matched in the hash table */
  if not h.find();
  /* Select which variables to output */
  keep secid date days delta impl_volatility impl_strike cp_flag;
run;

/* Sort the data */
proc sort data = volsurface1;
  by secid date secid impl_strike;
run;

/* Write out a CSV for each secid */
data _null_;
  set volsurface1;
  length fv $200;
  fv = "\path\to\output\" || trim(put(secid, 6.)) || ".csv";
  file write filevar = fv dsd dlm = ',' lrecl = 32000;
  put (_all_) (:);
run;

As I don't have your data this is untested. The only constraint I can see is that the contents of indexsecid must fit in memory. If you were not concerned with the order this could be all done in one data step.

Upvotes: 1

Related Questions