BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
9 REPLIES 9
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Yes - open a new forum thread first, and post a more appropriate SUBJECT.

Scott Barry
SBBWorks, Inc.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1394 views
  • 0 likes
  • 2 in conversation