SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

data requirement

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

data requirement

How can I obtain the dataset if the requirement is as follows?
If the time between one obs and the succeeding obs is shorter than 6 months, the succeeding obs will be deleted. For example the obs (19810630, 19811130, 19811231, 19820129…) will be deleted, and so on.

Part of the data is as shown below:

data have;
input entity date :yymmdd10. sales;
format date yymmdds10.;
cards;
entity DATE sales
abc 19800731 77.875
abc 19810430 71.375
abc 19810630 71.75
abc 19811030 75.75
abc 19811130 78.25
abc 19811231 80.25
abc 19820129 80.375
abc 19820430 85.25
abc 19820831 85.625
abc 19820930 95.125
abc 19821029 98
abc 19821130 106.125
abc 19831130 55.625
abc 19840731 52.375
abc 19840831 52.625
abc 19841031 56
abc 19850731 56.875
abc 19850830 57.25
abc 19851031 57.5
abc 19851129 63.375
abc 19851231 67.125
abc 19860331 69.75
abc 19860530 73.625
abc 19860630 77.875
abc 19870130 80
abc 19870331 88
abc 19870630 92
abc 19870831 98
abc 19880930 77.875
abc 19881031 80
abc 19881230 82.625
abc 19890131 87
abc 19890331 92.125
abc 19890428 92.5
abc 19890531 95.625
abc 19890630 102.75
xyz 19800930 17
xyz 19801031 17.375
xyz 19801128 17.375
xyz 19810227 17.375
xyz 19810331 18.75
xyz 19810430 20.375
xyz 19810529 23
xyz 19810630 24.25
xyz 19820831 19.75
xyz 19820930 22.125
xyz 19821029 23
xyz 19821130 23
xyz 19830331 24.75
xyz 19830429 25.875
xyz 19830630 30.375
xyz 19830729 30.75
xyz 19830930 33.375
xyz 19850131 16.25
xyz 19850430 18.875
xyz 19850531 19.625
xyz 19850628 19.75
xyz 19850731 19.75
xyz 19850830 19.75
;
Run;


Accepted Solutions
Solution
‎01-17-2018 08:27 AM
Super User
Posts: 6,642

Re: data requirement

Presumably, you want to keep the first observation for each ENTITY.  That requires slightly more programming:

 

data want;

  set have;

   by entity;

   if first.entity or dif(date) > 182;

run;

View solution in original post


All Replies
PROC Star
Posts: 1,218

Re: data requirement

Do something like this

 

data have;
input entity$ date :yymmdd10. sales;
format date yymmdds10.;
cards;
abc 19800731 77.875
abc 19810430 71.375
abc 19810630 71.75
abc 19811030 75.75
abc 19811130 78.25
abc 19811231 80.25
abc 19820129 80.375
abc 19820430 85.25
abc 19820831 85.625
abc 19820930 95.125
abc 19821029 98
abc 19821130 106.125
abc 19831130 55.625
abc 19840731 52.375
abc 19840831 52.625
abc 19841031 56
abc 19850731 56.875
abc 19850830 57.25
abc 19851031 57.5
abc 19851129 63.375
abc 19851231 67.125
abc 19860331 69.75
abc 19860530 73.625
abc 19860630 77.875
abc 19870130 80
abc 19870331 88
abc 19870630 92
abc 19870831 98
abc 19880930 77.875
abc 19881031 80
abc 19881230 82.625
abc 19890131 87
abc 19890331 92.125
abc 19890428 92.5
abc 19890531 95.625
abc 19890630 102.75
xyz 19800930 17
xyz 19801031 17.375
xyz 19801128 17.375
xyz 19810227 17.375
xyz 19810331 18.75
xyz 19810430 20.375
xyz 19810529 23
xyz 19810630 24.25
xyz 19820831 19.75
xyz 19820930 22.125
xyz 19821029 23
xyz 19821130 23
xyz 19830331 24.75
xyz 19830429 25.875
xyz 19830630 30.375
xyz 19830729 30.75
xyz 19830930 33.375
xyz 19850131 16.25
xyz 19850430 18.875
xyz 19850531 19.625
xyz 19850628 19.75
xyz 19850731 19.75
xyz 19850830 19.75
;
Run;

data want;
	set have;
	if dif(date) > 6*30;
run;
Solution
‎01-17-2018 08:27 AM
Super User
Posts: 6,642

Re: data requirement

Presumably, you want to keep the first observation for each ENTITY.  That requires slightly more programming:

 

data want;

  set have;

   by entity;

   if first.entity or dif(date) > 182;

run;

☑ This topic is solved.

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

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