- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
&n is the max number of each group .
SAS Documentation is the best teacher .Check PROC SUMMARY/MEANS 's doc .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please Try Proc Transpose, it is much simpler than using a macro to do this,
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.