01-29-2018 08:38 PM
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;
I'm quite new to macros, and not sure why the above doesn't work?
01-29-2018 08:50 PM
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:
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.
01-29-2018 09:25 PM
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;
01-30-2018 08:01 AM
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;
01-30-2018 10:54 AM
Thanks for the reply, this works. Could I ask what is happening in the PROC SUMMARY datastep? How exactly does the [&n] work?
01-31-2018 09:56 AM
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
01-31-2018 10:12 AM
@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
01-31-2018 10:53 AM - edited 01-31-2018 10:53 AM
@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.