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
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.
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.
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;
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;
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
&n is the max number of each group .
SAS Documentation is the best teacher .Check PROC SUMMARY/MEANS 's doc .
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
Please Try Proc Transpose, it is much simpler than using a macro to do this,
@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
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.