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

Hi.

I have a small problem with creating a dataset I need for an analysis.

Lets assume I have an ID variable  "serial", and two different string variables: - "period" in the form 201403

                                                                                                              - "period_renewed" in the same form

First what I want to do is to delete all data where period>period_renewed.

Further I want to keep only the observations from the last 6 periods from the point in time where period=period_renewed.

Someone out there might have a simple solution to this problem, but I'm completely blank as I'm quite new to using SAS.

Any feedback would be extremely welcome. Smiley Happy

/petter

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Assuming there are no duplicates :

proc sort data=have(where=( period<=period_renewed)) out=temp; by serial descending period; run;

data want;

i = 0;

do until (last.serial);

  set temp; by serial;

  i + 1;

  if i <= 6 then output;

  end;

drop i;

run;

proc sort data=want; by serial period; run;

(Untested)

PG

PG

View solution in original post

4 REPLIES 4
Michelle
Obsidian | Level 7

the following will create a dataset called subset that excludes anything where period>period_renewed:

data subset;

     set original;

     if period>period_renewed then delete;

run;

alternatively, you could use:

data subset;

     set original;

     where period<=period_renewed;

run;

not really sure what you are asking for in the second part.

PGStats
Opal | Level 21

Assuming there are no duplicates :

proc sort data=have(where=( period<=period_renewed)) out=temp; by serial descending period; run;

data want;

i = 0;

do until (last.serial);

  set temp; by serial;

  i + 1;

  if i <= 6 then output;

  end;

drop i;

run;

proc sort data=want; by serial period; run;

(Untested)

PG

PG
PGStats
Opal | Level 21

Or perhaps, simpler:

proc sort data=have; by serial period; run;

data want;

retain firstSerial;

set temp; by serial;

if first.serial then firstSerial = _n_;

if period>=period_renewed then do;

  do point = max(firstSerial, _n_ - 5) to _n_;

       set temp point=point;

       output;

       end;

  firstSerial = constant('BIG');

  end;

drop point firstSerial;

run;

(Untested)

PG

PG
petterco
Calcite | Level 5

Thank you both for your suggestions Smiley Happy 
I solved the problem through changing format of the string to a date variable via the input function,

and then used the intck function + an extra variable to select the right interval.

@PGstats I learned something new from you! Thanks

/Petter

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1099 views
  • 6 likes
  • 3 in conversation