DATA Step, Macro, Functions and more

create data rows depending on value in column

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

create data rows depending on value in column

Hello,

 

I have a data table that I need to create based off another table.  My original table looks like this:

TEXT LCNT
ABC   5
DEF   2
GHI   12      

I need to turn that into

ABC_1

ABC_2

ABC_3

ABC_4

ABC_5

DEF_1

DEF_2

GHI_1

GHI_2

GHI_3

GHI_4

GHI_5

GHI_6

GHI_7

GHI_8

GHI_9

GHI_10

GHI_11

GHI_12

 

What would be the best approach to loop using a variable value?  I thought of doing a do loop inside a data step but how can I set the variable value to loop through?

 


Accepted Solutions
Solution
‎07-29-2016 02:53 PM
Super User
Posts: 17,750

Re: create data rows depending on value in column

I don't think there's a need for macro variables here.

 

data have;
	input text $ lcnt;
	datalines;
abc 5
def 2
ghi 12
;
run;

data want;
	set have;

	do i=1 to lcnt;
		new_var=catx("_", text, i);
		output;
	end;
	
	keep new_var;
run;

View solution in original post


All Replies
Occasional Contributor
Posts: 19

Re: create data rows depending on value in column

Try this:

data have;

input text $ lcnt;

datalines;

abc 5

def 2

ghi 12

;

run;

 

data _null_;

set have;

call symput("new_cnt",max(lcnt));

run;

 

data want;

set have;

do i=1 to &new_cnt. until(lcnt<=i);

new_var=cats(text,"_",i);

output;

end;

run;

Solution
‎07-29-2016 02:53 PM
Super User
Posts: 17,750

Re: create data rows depending on value in column

I don't think there's a need for macro variables here.

 

data have;
	input text $ lcnt;
	datalines;
abc 5
def 2
ghi 12
;
run;

data want;
	set have;

	do i=1 to lcnt;
		new_var=catx("_", text, i);
		output;
	end;
	
	keep new_var;
run;
Super Contributor
Posts: 398

Re: create data rows depending on value in column

Thank you everyone who posted. This solution worked great. Thanks
Respected Advisor
Posts: 4,641

Re: create data rows depending on value in column

data want;
set have;
do i = 1 to lcnt;
	ID = catx("_", text, lcnt);
	output;
	end;
keep ID;
run;
PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 329 views
  • 2 likes
  • 4 in conversation