topic Re: How to generate N rows based on a value in a column? in SAS Programming

How to generate N rows based on a value in a column?

SteinOk — Thu, 30 Apr 2020 14:55:20 GMT

I am trying to create a new dataset based on an existing dataset.

My data contains a unique ID and number of observations (N_OBS) for each unique ID, as shown in the code below.

data have;
input ID $ N_OBS;
cards;
01 0
02 1
03 3
04 2
;
run;

I am trying to create a row for each unique value connected to the ID, so if ID='03' has 3 observations in N_OBS, i would like 3 rows with the ID '03' counting the number of observations OBS1-OBS3.

The end result i am looking for would be:

data want;
input ID $ OBS_ID $;
cards;
02 OBS1
03 OBS1
03 OBS2
03 OBS3
04 OBS1
04 OBS2
;
run;

I am uncertain how to approach this problem, and tips/help would be appreciated.

Re: How to generate N rows based on a value in a column?

PaigeMiller — Thu, 30 Apr 2020 15:02:48 GMT

data have;
input ID $ N_OBS;
length obs_id $ 16;
if n_obs>0 then do i=1 to n_obs;
obs_id=cat('obs',i);
output;
end;

drop i n_obs;
cards;
01 0
02 1
03 3
04 2
;
run;

Re: How to generate N rows based on a value in a column?

ballardw — Thu, 30 Apr 2020 15:17:46 GMT

@PaigeMiller

I may have spent too much time on this forum. My initial idea for a solution varied by exactly 2 characters from yours, basically style choices.

Re: How to generate N rows based on a value in a column?

SteinOk — Thu, 30 Apr 2020 15:18:15 GMT

Thank you, i am very pleased i was able to learn something new about how SAS handles if statements.

Re: How to generate N rows based on a value in a column?

PaigeMiller — Thu, 30 Apr 2020 15:20:30 GMT

@ballardw wrote:

@PaigeMiller

I may have spent too much time on this forum. My initial idea for a solution varied by exactly 2 characters from yours, basically style choices.

Alternative explanation: great minds think alike.

Alternative explanation 2: birds of a feather flock together