Creating changing variable names within %do loop

[MikeFranz]

Hi,

I have the following "oneapp.lease" dataset:

property_id lease_id rent_amount1 rent_amount2
1           1        100          200
1           2        110          150
1           3        120          300
2           4        105          .

And I would like to convert it to the following:

property_id lease_id1 lease1_rent1 lease1_rent2 lease_id2 lease2_rent1 lease2_rent2 lease_id3 lease_rent1 lease_rent2
1           1         100          200             2          110         150          3      120          300
2           4         105          .             .         .            .            .         .           .  

In short, I'm trying to convert from narrow to wide, with one observation per property_id, creating variables lease1_rent1 to leaseN_rentM

where there is a predefined max N and M (e.g. N= 10 and M = 3)

My code currently looks as follows:

%let i = 1;
%macro create_leases;
DATA lease_wide;
set oneapp.lease;
by property_id;
if first.property_id then
&i. = 1;
%do j = 1 %to &max_rent_changes.;
lease&i._rent_amount&j. = rent_amount&j.;
%end;
%let i = &i. + 1;
if last.property_id then
output;
RUN;
%mend;%create_leases()

I'm quite new to macros, and not sure why the above doesn't work?

Thanks,

Mike

[novinosrin]

to me it doesn't seem like a macro problem in the first place. You need a nice transpose "sas code" to achieve what you want. 

[Astounding]

The first thing to learn about macros is that they don't process data.  Instead, they construct a program.  When the constructed program runs, that processes the data.  

To give you an example from your non-working program, consider this combination:

%let i=1;

if first.property_id then &i.=1;

All macro language does is make the text substitution, to create a valid (or possibly invalid in this case) SAS statement.  That means your two statements produce:

if first.property_id then 1=1;

Clearly, this is not the SAS statement that you intended.  So the basic idea when learning macros is that you first construct a working program that does not use macros at all.  Then you consider how macro language could be used to generate that program.

[novinosrin]

Here is a demo of an approach you actually can think of and/or alternatively try arrays. I'm too lazy now but I am sure you can perfect the code in the demo with little modification

data have;
input property_id lease_id rent_amount1 rent_amount2 ;
datalines;
1           1        100          200
1           2        110          150
1           3        120          300
2           4        105          .
;

proc transpose data=have out=_have;
by property_id;
var lease_id rent_amount1 rent_amount2;
run;


proc transpose data=_have out=_have1;
by property_id _name_;
run;

data temp;
set _have1;
by 	property_id _name_;
if first._name_ then n=0;
n+1;
run;

proc sort data=temp out=_t;
by 	property_id n;
run;

proc transpose data=_t(keep=property_id col1) out=want;
by property_id ;
var col1;
run;

[Ksharp]

data have;
input property_id lease_id rent_amount1 rent_amount2 ;
datalines;
1           1        100          200
1           2        110          150
1           3        120          300
2           4        105          .
;

proc sql noprint;
select max(n) into : n
 from (select count(*) as n from have group by property_id);
quit;
proc summary data=have;
by property_id;
output out=want idgroup(out[&n] (lease_id rent_amount1 rent_amount2 )=);
run;

[MikeFranz]

Hi,

Thanks for the reply, this works. Could I ask what is happening in the PROC SUMMARY datastep? How exactly does the [&n] work?

Thanks again

[Ksharp]

&n is the max number of each group .

SAS Documentation is the best teacher .Check PROC SUMMARY/MEANS 's doc .

[art297]

While proc summary can definitely be used to make a long file wide, it doesn't end up with the variable names you wanted.

A group of us wrote a macro a couple of years ago that, I think, does exactly what you are trying to accomplish. You can download the macro from: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Once you download the macro and run it, the following call will accomplish the task:

%transpose(data=have, out=want, by=property_id, id=lease_id, var_first=no,
 prefix=lease, delimiter=_, var=rent_amount1 rent_amount2)

Art, CEO, AnalystFinder.com

[Satish_Parida]

Please Try Proc Transpose, it is much simpler than using a macro to do this,

[art297]

@Satish_Parida: Please explain your comment. The macro I proposed requires less typing, gives more control than proc transpose, and runs 50 to 100 times faster than proc transpose.

Art, CEO, AnalystFinder.com

[Satish_Parida]

@art297  The replay was over the macro created by the User.

To successfully execute the macro: user specified, as we know the maximum number of records are their datatypes, that can be achieved by using SAS arrays and lots of retain variables.

While A simple transpose over variable property_id will transpose the whole table to users prospective structure how ever, the column names wont be ideal to read.

Thank you for sharing the article.