Loop through list

Question

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

Why do you want to generate one dataset per secid? Usually it's simpler to keep everything in one big dataset and use by-group processing. — user667489, Apr 01 '15 at 12:28
Well, I want to export it to CSV and then read it in matlab and its easier for me if I have a CSV for each secid. — phdstudent, Apr 01 '15 at 12:32
How many secid values are there in your list? Do you want all the values that are present in another dataset? — user667489, Apr 01 '15 at 12:40
Use the filevar option in your export to split the file by secid. No macro needed. — Reeza, Apr 01 '15 at 13:02
Hi Reeza, thanks again for your comment. I attach my code below again. Can you help me in the syntax for referring to the table with secids and the syntax for export? — phdstudent, Apr 01 '15 at 13:26
Check my example here using the `filvevar` option to write out multiple csv files: http://stackoverflow.com/questions/28287459/sas-export-data-to-multiple-csv-file-by-year/28288064#28288064 — DWal, Apr 01 '15 at 13:41
Thanks Dwal. I will give it a try. What about the syntax for creating the table w/ secids and referring to it later? — phdstudent, Apr 01 '15 at 13:58
Following DWal's comment I tried to export using filvevar but no sucess... is my syntax correct? Thanks everyone for all comments and patience. — phdstudent, Apr 01 '15 at 16:22
Your `where` clause should have `a.secid=b.secid` and the format of your `put()` statement should be `6.` to match the length of the `secid`'s. Also, Please do not edit your solution into the question, as it will make less sense and be less helpful to users who come to the question later; post it as an answer instead. — SRSwift, Apr 01 '15 at 21:25

score 1 · Accepted Answer · edited May 23 '17 at 12:30

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.

score 0 · Answer 2 · answered Apr 02 '15 at 00:19

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

Loop through list

2 Answers2