Date: Thu, 10 Jul 2003 15:21:01 +0100
Reply-To: Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Subject: Re: Load dataset into a macro array
In-Reply-To: <200307091813.h69IDix12331@listserv.cc.uga.edu>
Hi Howard
As you and others have addressed the thread fully, may I refer to just :
>One possible weakness is that this depends on the insertions being done at
>the end of the table. I don't think that is documented behavior.
It is probable that the sas dataset option "NOreuse" is what you need to
ensure inserted rows appear only at the end of the table.
The documentation of the REUSE (system and dataset) option, links it to
compressed datasets only (though I'm not sure why !)
Regards
Peter
Howard Schreier <Howard_Schreier@ITA.DOC.GOV> writes
>I am very happy to stay out of the Macro Best Practice Symposium.
>
>Michael's original post spoke of the need to progress through 510 rows of
>the control table, advancing one row each time the program was run. He did
>not say that the runs would be in a single loop or even in a single SAS
>session. He also spoke of the need to do occasional reruns.
>
>Here's an approach to meeting those needs.
>
>First load the control information in a SAS dataset in a permanent library
>(assuming that session-to-session persistence is needed).
>
> libname perm 'perm';
>
> data perm.macro_var_values;
> input client $ segment $ analysis_year;
> serial ++ 1;
> cards;
> GM abc 2002
> GE srt 2003
> AA aaa 2003
> ZZ zzzz 1999
> ;
>
>Notice that I added a serial number to each observation. Next, create view
>which assigns serial numbers:
>
> data perm.ordered / view=perm.ordered;
> set perm.macro_var_values;
> rownum ++ 1;
> run;
>
>It is true that initially the two serial numbers will be equal. But the
>ones provided by the view are refreshed on the fly, while the others are
>locked along with the values of the other variables.
>
>Here is a trivial macro which represents Michael's inner code but actually
>does nothing except place the macrovariable values in the log:
>
> %macro afo;
> %put [Run code against &CLIENT / &SEGMENT / &ANALYSIS_YEAR];
> %mend afo;
>
>This macro can be used to verify the operation of the code shown below.
>
>Now here's the driver:
>
> proc sql noprint;
>
> create table thistime as
> select client, segment, analysis_year, serial
> from perm.ordered
> having rownum=min(rownum);
>
> select client, segment, analysis_year
> into :client, :segment, :analysis_year
> from thistime;
>
> delete from perm.macro_var_values
> where serial in (select serial from thistime);
>
> insert into perm.macro_var_values select * from thistime;
>
> quit;
>
>The first statement copies the first row of the control table into a
>temporary table. The next statement loads the macrovariables. The third
>statement deletes the row from the control table and the fourth statement
>immediately re-inserts it.
>
>The critical point is that the insertion is done at the end of the table.
>So after this code has run, the PERM.ORDERED looks like this:
>
> analysis_
> Obs client segment year serial rownum
>
> 1 GE srt 2003 2 1
> 2 AA aaa 2003 3 2
> 3 ZZ zzzz 1999 4 3
> 4 GM abc 2002 1 4
>
>The next time the code is run, another row is used to load the
>macrovariables before being rotated to the end of the queue.
>
>One possible weakness is that this depends on the insertions being done at
>the end of the table. I don't think that is documented behavior. Rather it
>may be no more than an implementation artifact. It should be possible to
>add a timestamp column to the control table and use it to guarantee the
>required row ordering.
>
>Here's another loader. This one reuses the most recently used set of values
>and does not alter the ordering of the rows in the table.
>
> proc sql noprint;
>
> select client, segment, analysis_year
> into :client, :segment, :analysis_year
> from perm.ordered
> having rownum=max(rownum);
>
> quit;
>
>On Tue, 8 Jul 2003 17:20:56 -0400, Dasappan, Michael
><Michael_Dasappan@MEDCOHEALTH.COM> wrote:
>
>>I have a dataset with three variables and 510 obs. I need to use these
>>values of all three variables in my program, but only one row at a time
>>
>>ex: client segment analysis_year
>> GM abc 2002
>> GE srt 2003
>>
>>I need to use var1, var2, var3 with the values GM,abc,2002 the first time I
>>run my program, and use the values from the second row, the next time I run
>>the program.
>>
>>How would I do this & once the values are loaded, how would I reference the
>>macros in my program.
>>
>>Currently I do this
>>
>>%AFO(CLIENT = 'GM', SEGMENT = 'HOURLY', ANALYSIS_YEAR = 'ANY');
>>
>>
>>
>>Also, I plan to run the program using these macro values and I expect it to
>>loop 510 times, but there might be times I might want to rerun only one row
>>out of this macro array.
>>
>>
>>
>>I know this is probably hoping for a lot, I would appreciate any help.
>>
>>
>>
>>
>>
>>
>>
>>
>>This e-mail message and any attachments contain confidential information
>from Medco Health Solutions, Inc. If you are not the intended recipient,
>you are hereby notified that disclosure, printing, copying, distribution,
>or the taking of any action in reliance on the contents of this electronic
>information is strictly prohibited. If you have received this e-mail
>message in error, please immediately notify the sender by reply message and
>then delete the electronic message and any attachments.
--
Peter Crawford
available for SAS consultancy contracts
| 
