topic Stretch data set into long format panel data in SAS Programming

Stretch data set into long format panel data

jkim197 — Tue, 19 Dec 2017 16:41:34 GMT

Hello SAS users.

I have a dataset looks like below.

crsp_fundno	begdt	enddt	crsp_obj_cd
1	19971231	19981230	ICQM
1	19981231	19991230	ICQY
1	19991231	20000731	ICQY
2	19971231	19981230	EDYG
2	19981231	19991230	EDYB
2	19991231	20000831	EDYB

What I want to do is to change this dataset into

crsp_fundno	date (month end)	crsp_obj_cd
1	19971231	ICQM
1	19980131	ICQM
1	19980228	ICQM
1	19980331	ICQM
1	19980430	ICQM
1	19980531	ICQM
1	19980630	ICQM
1	19980731	ICQM
1	19980830	ICQM
1	19980931	ICQM
1	19981030	ICQM
1	19981131	ICQM
1	19981231	ICQY
1	19990131	ICQY
1	19990228	ICQY
	…
	…
	…
2	19971231	EDYG
2	19980131	EDYG
2	19980228	EDYG
2	19980331	EDYG
2	19980430	EDYG
2	19980531	EDYG
2	19980630	EDYG
2	19980731	EDYG
2	19980830	EDYG
2	19980931	EDYG
2	19981030	EDYG
2	19981131	EDYG
	…

in this situation how do I manage my dataset??

data ex;
input crsp_fundno begdt enddt crsp_obj_cd $;
cards;
1	19971231	19981230	ICQM
1	19981231	19991230	ICQY
1	19991231	20000731	ICQY
2	19971231	19981230	EDYG
2	19981231	19991230	EDYB
2	19991231	20000831	EDYB
;
run;

Re: Stretch data set into long format panel data

Reeza — Tue, 19 Dec 2017 16:48:53 GMT

1. Import your dates as dates, not numbers

2. use INTCK to find the number of intervals between start and end date

3. Use a DO LOOP with INTNX and OUTPUT to output the number of records desired.

Untested

data want;
set have;

num_months = intck('month', start_date, end_date);

do i=1 to num_months;

date = intnx('month', start_date, i, 'e');

OUTPUT;

end;

RUN:

@jkim197 wrote:

Hello SAS users.

I have a dataset looks like below.

crsp_fundno begdt enddt crsp_obj_cd

1 19971231 19981230 ICQM

1 19981231 19991230 ICQY

1 19991231 20000731 ICQY

2 19971231 19981230 EDYG

2 19981231 19991230 EDYB

2 19991231 20000831 EDYB

What I want to do is to change this dataset into

crsp_fundno date (month end) crsp_obj_cd

1 19971231 ICQM

1 19980131 ICQM

1 19980228 ICQM

1 19980331 ICQM

1 19980430 ICQM

1 19980531 ICQM

1 19980630 ICQM

1 19980731 ICQM

1 19980830 ICQM

1 19980931 ICQM

1 19981030 ICQM

1 19981131 ICQM

1 19981231 ICQY

1 19990131 ICQY

1 19990228 ICQY

…

…

…

2 19971231 EDYG

2 19980131 EDYG

2 19980228 EDYG

2 19980331 EDYG

2 19980430 EDYG

2 19980531 EDYG

2 19980630 EDYG

2 19980731 EDYG

2 19980830 EDYG

2 19980931 EDYG

2 19981030 EDYG

2 19981131 EDYG

…

in this situation how do I manage my dataset??
data ex;
input crsp_fundno begdt enddt crsp_obj_cd $;
cards;
1	19971231	19981230	ICQM
1	19981231	19991230	ICQY
1	19991231	20000731	ICQY
2	19971231	19981230	EDYG
2	19981231	19991230	EDYB
2	19991231	20000831	EDYB
;
run;

Re: Stretch data set into long format panel data

jkim197 — Wed, 20 Dec 2017 08:05:45 GMT

Thank You Reeza it works really well !!