Help using Base SAS procedures

Creating multiple rows from 1 record

Reply
Occasional Contributor
Posts: 11

Creating multiple rows from 1 record

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

Obs

1

2

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.....

Frequent Contributor
Posts: 89

Re: Creating multiple rows from 1 record

DATA out(DROP=i);    

DO obs=1 TO 5;      

DO i=0 TO 4;        

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

OUTPUT;      

END;    

END;    

FORMAT date ddmmyy10.;

RUN;

SAS Employee
Posts: 105

Re: Creating multiple rows from 1 record

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;

1

2

3

4

;

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;

end;

format month mmyyn6.;

run;

PROC Star
Posts: 7,474

Re: Creating multiple rows from 1 record

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?

Occasional Contributor
Posts: 11

Creating multiple rows from 1 record

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.

Ask a Question
Discussion stats
  • 4 replies
  • 1037 views
  • 6 likes
  • 4 in conversation