topic Creating multiple rows from 1 record in SAS Procedures

Creating multiple rows from 1 record

uksusu — Thu, 25 Aug 2011 09:02:03 GMT

I have a list of unique observations that I want to assign a month to for several years

Obs

3 etc.

I want it to look like

Obs

1 Jan05

1 Feb05

1 Mar05....

.....

1 Jul11

2 Jan05

2 Feb05 and so on.

Can anyone help with the most efficient way of doing this.

Thanks.....

Re: Creating multiple rows from 1 record

Jaheuk — Thu, 25 Aug 2011 09:49:45 GMT

DATA out(DROP=i);

DO obs=1 TO 5;

DO i=0 TO 4;

date=INTNX('MONTH','01jan2005'd,i,'B');

OUTPUT;

END;

FORMAT date ddmmyy10.;

RUN;

Re: Creating multiple rows from 1 record

yonib — Thu, 25 Aug 2011 09:58:30 GMT

Hi,

In the following program you need to set the start year and the end_year that you want to use:

data temp;

input obs;

cards;

;

run;

%let start_year=2005;

%let end_year=2011;

options symbolgen;

data temp2(drop= j i);

set temp;

do i=&start_year to &end_year;

do j=1 to 12;

month=mdy(j,01,i);

output;

end;

format month mmyyn6.;

run;

Re: Creating multiple rows from 1 record

art297 — Thu, 25 Aug 2011 13:39:40 GMT

I see that others have already shown you how to build a series of consecutive monthly periods, but I have what I think is an equally pressing question: how do you know which date applies to which of your records?

Creating multiple rows from 1 record

uksusu — Thu, 25 Aug 2011 14:49:16 GMT

For all of my observations I needed to create an extra row for each month since Jan05 with the obs against it; so each observation would appear 79 times (number of months since Jan05).

I have additional data at obs level but only appearing in certain months; but my customer wants to see all months even if the observation does not have a record against it.

With my original data now I can do a simple merge by obs & date to keep all obs for all months.

I had actually created what I required before posting, but it seemed such a convoluted solution, that I thought there must be an easier way; and there was!

Both options worked fine, but I went with the yonib's in the end.

Thanks to everyone who replied.