SAS Programming

DATA Step, Macro, Functions and more
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-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.

 

Early bird rate extended! Save $200 when you sign up by March 31.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 4796 views
  • 2 likes
  • 6 in conversation