BookmarkSubscribeRSS Feed
MikeFranz
Quartz | Level 8

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

 

 

 

10 REPLIES 10
novinosrin
Tourmaline | Level 20

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
PROC Star

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
Tourmaline | Level 20

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
Super User
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
Quartz | Level 8

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
Super User

&n is the max number of each group .

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

art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

@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
Lapis Lazuli | Level 10

@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.

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
  • 10 replies
  • 3341 views
  • 2 likes
  • 6 in conversation