topic Re: Macro loop help needed in SAS Programming

Macro loop help needed

gsnidow — Fri, 07 Sep 2012 20:00:44 GMT

Greetings all. I am trying to write a macro with a loop to read files on our mainframe, and I have become terribly confused by the macro syntax. Basically there are 20 files I need to read, and they are denoted by a (0) through (-19). Below is the code to manually get each file by changing the file name (Thank you Tom for pointing out the binary variable)

Rsubmit;
FILENAME ezt 'PPPCTK.ZZZ.BALANCED.REVXREC.DSKO745(-0),' DISP=SHR;
data work.ontime;
INFILE ezt;
input
@1   ID_BA      PD6.
@7 CD_BUS    $4.
@16 DT_BILL    $10.
@48 AT_SVC_UNIT_BILLED PD6.2
@100 CD_RATE    $3.
@138 CD_REV_SRC   $2.
@154 CD_CYCLE   IB2.
@201 FL_MORE_REV_REC $1.;
run;

Proc Download Data=work.ontime OUT=work.ontime;
QUIT;
Run;

Now, I would like the macro to change the '0' in the parentheses after the file name to every value between 0 and 19, and to create a table in work that is appended with that number. Does this make sense? Is it possible? Do I need to include more info? Thank you.

Greg

Re: Macro loop help needed

Linlin — Fri, 07 Sep 2012 20:11:30 GMT

%macro test;
%do i=0 %to 19;
FILENAME ezt "PPPCTK.ZZZ.BALANCED.REVXREC.DSKO745(-&i)," DISP=SHR;
data work.ontime&i;
INFILE ezt;
input
@1   ID_BA      PD6.
@7 CD_BUS    $4.
@16 DT_BILL    $10.
@48 AT_SVC_UNIT_BILLED PD6.2
@100 CD_RATE    $3.
@138 CD_REV_SRC   $2.
@154 CD_CYCLE   IB2.
@201 FL_MORE_REV_REC $1.;
run;

Proc Download Data=work.ontime&i OUT=work.ontime&i;
QUIT;
Run;
%end;
%mend;
%test

Re: Macro loop help needed

art297 — Fri, 07 Sep 2012 20:14:34 GMT

Unless you want to overwrite the same file nineteen times on both the server and the client, I would assume that you don't always want to call the files 'work.ontime'

How do you want to name them? If Linlin's proposed renaming works for you then I would just add one more thing, namely to put your rsubmit statement before the macro declaration and the endrsubmit statement (which you omitted from your example) at the very bottom of the code.

Re: Macro loop help needed

gsnidow — Fri, 07 Sep 2012 20:21:24 GMT

Arthur, they need to be named exactly how Linlin has done it. Thanks.

Re: Macro loop help needed

gsnidow — Fri, 07 Sep 2012 20:24:05 GMT

Linlin, thank you for the help. That looks almost exactly what I was trying at one point. It looks like the only thing missing is the Rsubmit, since I am running this on the mainframe first, then downloading the tables to my pc. I tried adding Rsubmit; to the macro, but I get the following error...

WARNING: Apparent symbolic reference I not resolved.

NOTE: Remote submit to MVSA complete.

Where should I put the Rsubmit within the macro? Thank you.

Greg

Re: Macro loop help needed

Tom — Fri, 07 Sep 2012 20:30:17 GMT

One way to wrap the whole thing into a single RSUBMIT/ENDRSUBMIT block. Then the macro is defined on the remote session and runs on the remote session. Make sure to include a ; so that remote session knows to start the macro rather than wait for parameter values.

rsubmit;

%macro test;

.....

%mend;

%test;

endrsubmit;

Note: I normally also add STATUS=N option to the PROC DOWNLOAD statement to prevent the pop up window with the status showing the percent downloaded. For small files the time to open and close the windows doubles the time to download. For large files I would normally be submitting in background anyway so the status windows are annoying.

Re: Macro loop help needed

art297 — Fri, 07 Sep 2012 21:38:14 GMT

You never responded to let everyone know whether you got it working. Based on your error, I presume that you didn't change the quotes around the filename, to be double rather than single quotes.

Re: Macro loop help needed

gsnidow — Mon, 10 Sep 2012 13:31:42 GMT

Arthur, I did not get it working yet, but I think I have all I need to get it partially working. I have encountered another problem. After 10 generations of the file, it gets archived to another volumne, and when using TSOD to access these files, the user is prompted to type ether a 'Y' or 'N' for whether or not to restore the file. I probably wont' have time to work on it today, but I will certainly post back when/if all works. Thank you.

Greg

Re: Macro loop help needed

gsnidow — Mon, 10 Sep 2012 17:24:12 GMT

Greetings all. It works, and indeed I did not change my single quotes to double quotes as Arthur suggested. I was also missing a semicolon in one spot. Anyhow, thank you all for your valuable help. I still have the issue of being prompted for an input after a certain generation of the file. I can avoid this by simply running this once per week I guess.

Greg

libname Test 'C:\sas_files\Test';

rsubmit;

%macro test;

%do i=0 %to 4;

FILENAME ezt "PPPCTK.ZZZ.BALANCED.REVXREC.DSKO745(-&i)" DISP=SHR;

data work.ontime&i;

INFILE ezt;

input

@1 ID_BA PD6.

@7 CD_BUS $4.

@16 DT_BILL $10.

@48 AT_SVC_UNIT_BILLED PD6.2

@100 CD_RATE $3.

@138 CD_REV_SRC $2.

@154 CD_CYCLE IB2.

@201 FL_MORE_REV_REC $1.;

run;

Proc Download Data=work.ontime&i OUT=test.ontime&i;

Run;

%end;

%mend;

%test;

endrsubmit;