Date: Mon, 10 Nov 2008 22:01:47 -0500
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>"
<schreier.junk.mail@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>"
<schreier.junk.mail@GMAIL.COM>
Subject: Re: MACRO control problem
On Mon, 10 Nov 2008 11:24:12 -0800, dc353@hotmail.com <dc353@HOTMAIL.COM> wrote:
>On Nov 8, 5:04 pm, hs AT dc-sug DOT org ("Howard Schreier)" wrote:
>> On Sat, 8 Nov 2008 11:29:19 -0800, dc...@hotmail.com <dc...@HOTMAIL.COM>
wrote:
>> >On Nov 5, 12:18 pm, snoopy...@GMAIL.COM (Joe Matise) wrote:
>> >> You're overwriting CNT1 each time. CNT1 does not exist separately for
each
>> >> dataset, it exists only once. You need three names if you're doing it
this
>> >> way.
>>
>> >> You might want to explain your ultimate purpose - this might not be
the best
>> >> way to go about that anyway.
>>
>> >> -
>> >> Joe
>>
>> >> On Wed, Nov 5, 2008 at 1:51 PM, dc...@hotmail.com <dc...@hotmail.com>
wrote:
>> >> > Hi,
>>
>> >> > I have the following code that refuses to pass each numeric
>> >> > observation into a macro variable.
>>
>> >> > There are four files:
>>
>> >> > risk.f_name contains variable fname with 3 observations:
>> >> > risk.file1
>> >> > risk.file2
>> >> > risk.file3
>>
>> >> > file1,file2 and file3 each contain variable cnt with 1 observation in
>> >> > each file
>>
>> >> > file1
>> >> > 1
>>
>> >> > file2
>> >> > 25
>>
>> >> > file3
>> >> > 101
>>
>> >> > I read the F_name file for the file name and pass that into a macro
>> >> > with call execute and then I try to read cnt from the file and pass it
>> >> > into a macro variable &cnt1. It looks like only the observation from
>> >> > file3 is getting passed into the macro. Here's the code:
>>
>> >> > %macro fam_data (tmp_dxata);
>> >> > data _null_;
>> >> > set &tmp_dxata;
>> >> > put 'cnt = ' cnt;
>> >> > call symput ('cnt1',trim(left(put(cnt,best12.))));
>> >> > %put '&cnt = ' &cnt1;
>> >> > run;
>> >> > %mend fam_data;
>>
>> >> > data _null_;
>> >> > set risk.f_name;
>>
>> >> > %let tmp_data = fname;
>>
>> >> > call execute('%fam_data('|| &tmp_data ||')');
>>
>> >> > put 'test1 ' &tmp_data;
>> >> > run;
>>
>> >> > and here's the log:
>>
>> >> > 142 %macro fam_data (tmp_dxata);
>> >> > 143 data _null_;
>> >> > 144 set &tmp_dxata;
>> >> > 145 put 'cnt = ' cnt;
>> >> > 146 call symput ('cnt1',trim(left(put(cnt,best12.))));
>> >> > 147 %put '&cnt = ' &cnt1;
>> >> > 148 run;
>> >> > 149 %mend fam_data;
>> >> > 150
>> >> > 151
>> >> > 152
>> >> > 153
>> >> > 154
>> >> > 155 data _null_;
>> >> > 156 set risk.f_name;
>> >> > 157
>> >> > 158 %let tmp_data = fname;
>> >> > 159
>> >> > 160 call execute('%fam_data('|| &tmp_data ||')');
>> >> > 161
>> >> > 162 put 'test1 ' &tmp_data;
>> >> > 163 run;
>>
>> >> > '&cnt = ' 101
>> >> > test1 risk.file1
>> >> > '&cnt = ' 101
>> >> > test1 risk.file2
>> >> > '&cnt = ' 101
>> >> > test1 risk.file3
>> >> > NOTE: There were 3 observations read from the data set RISK.F_NAME.
>> >> > NOTE: DATA statement used:
>> >> > real time 0.00 seconds
>> >> > cpu time 0.00 seconds
>>
>> >> > NOTE: CALL EXECUTE generated line.
>> >> > 1 + data _null_; set risk.file1; put 'cnt = ' cnt; call symput
>> >> > ('cnt1',trim(left(put(cnt,best12.)))); run;
>>
>> >> > cnt = 1
>> >> > NOTE: There were 1 observations read from the data set RISK.FILE1.
>> >> > NOTE: DATA statement used:
>> >> > real time 0.00 seconds
>> >> > cpu time 0.00 seconds
>>
>> >> > 2 + data _null_; set risk.file2; put 'cnt = ' cnt; call symput
>> >> > ('cnt1',trim(left(put(cnt,best12.)))); run;
>>
>> >> > cnt = 25
>> >> > NOTE: There were 1 observations read from the data set RISK.FILE2.
>> >> > NOTE: DATA statement used:
>> >> > real time 0.00 seconds
>> >> > cpu time 0.00 seconds
>>
>> >> > 3 + data _null_; set risk.file3; put 'cnt = ' cnt; call symput
>> >> > ('cnt1',trim(left(put(cnt,best12.)))); run;
>>
>> >> > cnt = 101
>> >> > NOTE: There were 1 observations read from the data set RISK.FILE3.
>> >> > NOTE: DATA statement used:
>> >> > real time 0.00 seconds
>> >> > cpu time 0.00 seconds
>>
>> >> > Been working on this for two days and can't figure out what I'm doing
>> >> > wrong. Seems like the problem has to be connected to process flow
>> >> > control. Help!- Hide quoted text -
>>
>> >> - Show quoted text -
>>
>> >Thanks for your reply. I tried to simplfy the problem down to what's
>> >not working. I have a dataset that contains the name of n datasets.
>> >stored in each of these datasets is one observation that provides a
>> >count that I need to mainpulate. I want to itierate through the file
>> >with the names of each dataset and at each observtion pass the
>> >observation name into a macro. Once in the macro I want to read the
>> >dataset and use the count as a macro variable. I know that I am
>> >overwritting the macro variable, I know that I can't use symput in
>> >this manner. I also don't know how to solve this problem. When I'm
>> >in the macro I want to read the dataset that I pass into the macro and
>> >then read in the one observation in that dataset and create a macro
>> >variable with it. What's confusing is that I can pass the file name,
>> >read the dataset, read the observation I'm looking for but I can't get
>> >it into a macro variable except in a way that overwrites all
>> >intermediate value or all prior itierations.
>>
>> I suspect that there are better ways to organize your data and avoid this
>> kind of intricacy. Meanwhile, I think the key is to include a second
>> parameter for the macro, to convey the sequence number. Try this:
>>
>> libname risk 'c:\temp';
>>
>> data risk.f_name;
>> input fname : $12.;
>> cards;
>> risk.file1
>> risk.file2
>> risk.file3
>> ;
>>
>> data risk.file1; cnt = 1; run;
>> data risk.file2; cnt = 25; run;
>> data risk.file3; cnt = 101; run;
>>
>> %macro fam_data (data=,serial=);
>> data _null_;
>> set &data;
>> call symputx ( cats('cnt' , &serial) , cnt );
>> run;
>> %mend fam_data;
>>
>> data _null_;
>> set risk.f_name;
>> call execute( cats( '%fam_data(data='
>> , fname
>> , ',serial='
>> , _n_
>> , ')'
>> )
>> );
>> run;;
>>
>> %put _user_;
>>
>> An alternative is to get rid of the macro and it all with CALL EXECUTE. That
>> way you spawn just one DATA step, not N.
>>
>> data _null_;
>> set risk.f_name end=lastobs;
>> if _n_=1 then call execute( 'data _null_;' );
>> call execute (cats( cat ( 'set ' , fname)
>> , ';'
>> , 'call symputx("cnt'
>> , _n_
>> , '",'
>> , 'cnt'
>> , ');'
>> )
>> );
>> if lastobs then call execute ('stop; run;');
>> run;- Hide quoted text -
>>
>> - Show quoted text -
>
>Howard,
>
>Thank you very much for your examples. While the approaches are
>different they both are doing the same thing. Creating a different
>macro variable for each observation i.e. &cnt1 &cnt2 &cnt3. I need
>to reuse the same macro variable or else I will need to learn how to
>use an array (I think).
Somehow I got the impression that you *wanted* a sequence of numerically
suffixed macro variables (arrays per se are a DATA step creature, not a
macro language one).
Here's a variation where a single macro variable is re-used after doing in
its job (in this case controlling the number of observations printed).
libname risk 'c:\temp';
data risk.f_name;
input fname : $12.;
cards;
risk.file1
risk.file2
risk.file3
;
data risk.file1; cnt = 1; run;
data risk.file2; cnt = 5; run;
data risk.file3; cnt = 11; run;
%macro fam_data (data=,serial=);
data _null_;
set &data;
call symputx ( 'cnt' , cnt );
run;
proc print data=sashelp.class(obs=&cnt); run;
%mend fam_data;
data _null_;
set risk.f_name;
call execute( cats( '%fam_data(data='
, fname
, ',serial='
, _n_
, ')'
)
);
run;;
%put _user_;
>
>I've attached the actual program with all it's worts so you can see
>the entire problem.
I don't have the time or patience to study and reverse-engineer all of this.
A streamlined yet fully representative example is better.
>data risk.pas_uni01;
>set risk.pas_uni00;
>rename
> M_DATE = MDTE
> FUND_NAME = FND_NAM
> FUND_FAMILY = FAMILY
> MSTAR_ID = MSTARID
> SHARE_C = PRMY_SHRC
> SAI_PEERGROUP_ID = SAI_PGID
> PEER_GROUP = SAI_PGNM
> NET_CUM_M1 = MRET;
>
>data risk.pas_uni01;
>set risk.pas_uni01;
>LABEL
> MDTE = MDTE
> FND_NAM = FND_NAM
> FAMILY = FAMILY
> MSTARID = MSTARID
> PRMY_SHRC = PRMY_SHRC
> SAI_PGID = SAI_PGID
> SAI_PGNM = SAI_PGNM
> MRET = MRET
> TNA = TNA ;
> run;
>
>data risk.pas_uni01;
>retain MDTE FAMILY FND_NAM FUND_ID SPORT_INSTRUMENT_ID MSTARID
> PRMY_SHRC MCATN SAI_PGNM SAI_PGID IS_FID IS_CAP TNA MRET;
>set risk.pas_uni01;
>run;
>
>Proc sort data=risk.pas_uni01;
> by family sai_pgid mstarid;
>run;
>
>proc sql;
> create table risk.pas_uni02 as
> select *, min(mdte) as MIN_DTE
> from risk.pas_uni01
> group by mstarid;
>quit;
>
>proc sort data=risk.pas_uni02;
> by family sai_pgid min_dte mdte;
>run;
>
>data risk.pas_uni02;
>retain MIN_DTE;
>set risk.pas_uni02;
>if family ='+' then family = 'NOFAMILY';
>run;
>
>proc sql outobs=5 ;
>create table family as
>select distinct family
>from risk.pas_uni02;
>quit;
>
>
>%macro fam_data(fam_str,tmp_data);
>data &tmp_data;
>set risk.pas_uni02;
>where family = &fam_str ;
>run;
>
>
>
>proc sort data=&tmp_data;
> by sai_pgnm mdte min_dte;
>run;
>%let tmp_tran=&tmp_data._trp;
>%put &tmp_tran;
>proc transpose data= &tmp_data out=&tmp_tran;
> id mstarid;
> var mret;
> by sai_pgnm mdte;
>run;
>
>data &tmp_tran;
>drop mdte _name_ _label_;
>set &tmp_tran;
>run;
>%let tmp_corr = &tmp_data._cor;
>%put &tmp_corr;
>proc corr data=&tmp_tran outp=&tmp_corr noprint;
> by sai_pgnm;
>run;
>
>data &tmp_corr;
>set &tmp_corr;
> if _type_ in ('MEAN' 'STD' 'N') then delete;
>run;
>%let tmp_cnt = &tmp_data._cnt;
>proc sql noprint;
> create table &tmp_cnt as
> select count(distinct _NAME_) as cnt
> from &tmp_corr;
>quit;
>run;
>data _null_;
>set &tmp_cnt;
>put 'test ' cnt;
>ids_obs =cnt;
>call symput('cnt1',ids_obs);
>%put &cnt1;
>run;
>%put &cnt1;
>
>%let mstar_ids = &tmp_data._ids;
>%put &mstar_ids;
>data &mstar_ids ;
>keep _NAME_;
>set &tmp_corr;
> if _N_ <= &cnt1 then output;
>run;
>
>proc sql noprint;
> select trim(_NAME_) into:retainlist separated by ' ' from
>&mstar_ids;
> quit;
>run;
>
>proc sort data=&tmp_corr;
>by sai_pgnm;
>run;
>%let tmp_col = &tmp_data._col;
>%put &tmp_col;
>
>data &tmp_col(KEEP=sai_pgnm NAME1 NAME2 COR cnt);
> set &tmp_corr (DROP=_TYPE_ RENAME=(_NAME_=NAME1));
> retain cnt;
> by SAI_PGNM;
> array mstarid[&cnt1] &retainlist;
> if first.sai_pgnm then cnt = 0;
> cnt=cnt+1;
> do row_id = cnt + 1 to DIM(mstarid);
> COR = mstarid[row_id];
> NAME2 = VNAME(mstarid(row_id));
> output;
> end;
>run;
>
>data &tmp_col;
>set &tmp_col;
> if cor = . then delete;
> if cor < .99 then delete;
>run;
>
>proc append base=risk.prmy_cor data=&tmp_col;
>run;
>%mend fam_data;
>
>
>
>data _null_;
>set family;
>fam_str = "'"||family||"'";
>ahold = substr(family,1,3);
>%let tmp_data = ahold;
>call execute('%fam_data('|| fam_str || ',' || &tmp_data ||')');
>run;
| 
