Return variable from a macro

[deleted_user]

This is my first SAS assgnmt so my question is really basic.
I have a table which can has 2 fields Fld, Attr. Now I am doing a transpose on this table, so the no. of 'Fld' and 'Attr' really depends on data. eg. Fld1, Fl2...Attr1 Attr2 etc.

Now I need macro which will count how many of FLd/Attr variables are there and then pass this to the following piece of code as iterations. So instead of having the loop as 1 to 5, I want to pass the iterations to it.
Basically, it does not recognise fld/attr variable as the number is not suffixed.
proc sql ;
select distinct attr, count(*) from mydata.e_joinf;
quit;


data MYDATA.e_final;
set mydata.e_join;
array fld(5);
array attr(5);
do j=1 to 5;
fld_txt = fld(j);
fld_name = attr(j);
msg_txt =tranwrd(msg_txt,trim(fld_name),trim(fld_txt));
msg_txt = compress(msg_txt,'()');
end;
run;

Can someone please help me out?

Thanks! Message was edited by: sasnewbee

[sbb]

You don't need a macro just to tell you how many variables you have, presuming that the named variables have a unique variable prefix, such as you have mentioned.

An ARRAY statement can declare a SAS variable prefix using a trailing colon character -- then you can use the DIM() function in a DO statement to iterate through your SAS variable list without listing the variables each by name.

Here's a simple example:

DATA _null_;
retain v1-v5 1;
array av_vars (*) v: ;
do i=1 to dim(av_vars);
putlog av_vars(i)= ;
end;
putlog _all_;
stop;
run;

Scott Barry
SBBWorks, Inc.

SAS SGF/SUGI conference paper on the topic:

http://support.sas.com/rnd/papers/sgf07/arrays1780.pdf Message was edited by: sbb

[deleted_user]

I did try using the colon and then the variable name but still the log says variallble attr not found.
proc sql ;
select count(distinct attr) into: vars from mydata.e_csa.

And secondly when we say retain v1-v5 , are we not hardcoding the maximum value to 5?
I am going thru the link too , to find if I get any clues. Message was edited by: sasnewbee

[sbb]

Please review and ingest the sample code provided in the prior reply - it works..... You want to leverage SAS programming techniques as much as possible. One way is to avoid using macro variables and the PROC SQL altogether, as suggested.

And, if you tried some code and didn't get expected results, suggest you share the SAS log with all output revealed -- also you may find the problem source by activating the most diagnostic output, with this stmt:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC MPRINT;



Scott Barry
SBBWorks, Inc.

[deleted_user]

Yes this code works. What I was thinking earlier was i still have to find the count using SQL and then use the dim function. But this was easy. I did get the results but still need a few changes.
Can you please let me know if I need the retain statement? I read that the retain stmt is used to retain previous values. So I changed that stmt to retain attr1-attr5.
I got an error : retain attr1-attr5 1;
ERROR: 1 and attr1 are incompatible for retain.
ERROR: 1 and attr2 are incompatible for retain.

I also get 3 additional columns: Fld_name,fld_txt and i. IS there any way to drop these?

data MYDATA.e_finalf;
set mydata.e_joinf;
*retain v1-v5 1;
array fld_nm_var (*) attr: ;
array fld_txt_var(*) fld:;
do i=1 to dim(fld_nm_var);
fld_txt = fld_txt_var(i);
fld_name = fld_nm_var(i);
msg_txt =tranwrd(msg_txt,trim(fld_name),trim(fld_txt));
msg_txt = compress(msg_txt,'()');
PUT '****';
end;
run;

[sbb]

Why do you think you need a RETAIN statement at all? There you go again with that hardcoded "5" suffix.....

Also, you can use either DROP or KEEP statement, as preferred. Note that when both are coded, DROP overrides KEEP when they both list the same variable.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Thanks Scott. I removed the retain statement and also added the Drop.
But I was wondering why SAS appends new columns to an existing dataset?
I was also under the assumption that Drop stmts etc will work only in a Proc SQL.

[sbb]

If you have a DATA step and you derive a new SAS variable, that variable is added to the SAS member(s) on the DATA statement, unless you use a DROP, KEEP statement or a DROP= / KEEP= dataset_option.

Scott Barry
SBBWorks, Inc.

Step-by-Step Programming with Base SAS(R) Software
--Introduction to DATA Step Processing
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001302699.htm

[deleted_user]

That explains it! Thank you so much.
I have an issue when the Tranwrd fundtion is passed dates. Though the input to the Tranwrd is 7/14/09 , the output is 71409. The same thing happens with brackets.If the input text has some brackets, the output does not have them.

Any suggestions?

[sbb]

Yes - open a new forum thread first, and post a more appropriate SUBJECT.

Scott Barry
SBBWorks, Inc.