DATA Step, Macro, Functions and more

Stretch data set into long format panel data

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Stretch data set into long format panel data

Hello SAS users.

I have a dataset looks like below.

 

crsp_fundnobegdtenddtcrsp_obj_cd
11997123119981230ICQM
11998123119991230ICQY
11999123120000731ICQY
21997123119981230EDYG
21998123119991230EDYB
21999123120000831EDYB

 

 

 

What I want to do is to change this dataset into

 

crsp_fundnodate (month end)crsp_obj_cd
119971231ICQM
119980131ICQM
119980228ICQM
119980331ICQM
119980430ICQM
119980531ICQM
119980630ICQM
119980731ICQM
119980830ICQM
119980931ICQM
119981030ICQM
119981131ICQM
119981231ICQY
119990131ICQY
119990228ICQY
  
   
   
219971231EDYG
219980131EDYG
219980228EDYG
219980331EDYG
219980430EDYG
219980531EDYG
219980630EDYG
219980731EDYG
219980830EDYG
219980931EDYG
219981030EDYG
219981131EDYG
  

 

 

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;

 


Accepted Solutions
Solution
‎12-20-2017 03:05 AM
Super User
Posts: 24,025

Re: Stretch data set into long format panel data

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;

 


 

View solution in original post


All Replies
Solution
‎12-20-2017 03:05 AM
Super User
Posts: 24,025

Re: Stretch data set into long format panel data

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;

 


 

Contributor
Posts: 32

Re: Stretch data set into long format panel data

 

Thank You Reeza it works really well !!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 152 views
  • 3 likes
  • 2 in conversation