BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vickyCh
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

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;
Astounding
PROC Star

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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