topic Re: create data rows depending on value in column in SAS Programming

create data rows depending on value in column

jerry898969 — Fri, 29 Jul 2016 16:39:52 GMT

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?

Re: create data rows depending on value in column

PBsas — Fri, 29 Jul 2016 17:08:04 GMT

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;

Re: create data rows depending on value in column

Reeza — Fri, 29 Jul 2016 17:12:29 GMT

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;

Re: create data rows depending on value in column

PGStats — Fri, 29 Jul 2016 17:34:09 GMT

data want;
set have;
do i = 1 to lcnt;
	ID = catx("_", text, lcnt);
	output;
	end;
keep ID;
run;

Re: create data rows depending on value in column

jerry898969 — Fri, 29 Jul 2016 18:54:03 GMT

Thank you everyone who posted. This solution worked great. Thanks