Trying to preprocess the sas dataset

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Trying to preprocess the sas dataset

Hi, I am newbie to SAS and i need help desperately.

 

getting straight to the point, I am trying to process the sas table into another table as described below:

 

 

Given:

data work;
input ID JOB_CD YEAR;
cards;
1 4 2006
1 4 2007
1 3 2008
1 5 2009
1 4 2010
2 5 2005
2 5 2006
7 1 2007

;
run;

 

 

WANT:

 

ID     JOB_CD    YEAR   FIRST_YEAR    LAST_YEAR

1            4             2006          2006                  2007

1            4             2007          2006                  2007

1            3             2008          2008                  2008

1            5             2009          2009                  2009

1            4             2010          2010                  2010

2            5             2005          2005                  2006

2            5             2006          2005                  2006

7            1             2007          2007                  2007

 

 

the actual data is much larger but,

basically I am trying to group the data by ID and JOB_CD, and get first_year and last_year for each job within each ID.

It seemed to work with using "BY ID JOB_CD", FIRST.ID, LAST.ID, RETAIN statements but the problem is, given the case above,

 

ID       JOB_CD       YEAR      FIRST_YEAR     LAST_YEAR

1             4                2006             2006                  2010

 

but as you can see this is not what i want. This guy with ID 1 had had a job 4 then changed a job for a few years before changing back to the original job.

 

It does not matter if I use SQL for it.

Simpler the better, but I really want this to work.

 

Thanks for reading.


Accepted Solutions
Solution
‎04-05-2018 04:37 AM
Super User
Posts: 10,238

Re: Trying to preprocess the sas dataset

Posted in reply to Astounding

Be cautious. Just grouping by id and job_cd will not solve the fact that id 1 has two separate periods with job_cd = 4.

I propose some trickery:

data
  int1 (keep=id job_cd period year)
  int2 (keep=id period first_year last_year)
;
set work;
by id job_cd notsorted;
retain period_count first_year;
if first.id then period = 0;
if first.job_cd
then do;
  period + 1;
  first_year = year;
end;
if last.job_cd
then do;
  last_year = year;
  output int2;
end;
output int1;
run;

data want;
merge
  int1
  int2
;
by id period;
drop period;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 6,768

Re: Trying to preprocess the sas dataset

While there are a couple of ways to do this, here are some tools that I expect would be "next in line" for you to learn.  You can skip the PROC SORT if the data set is already in the proper order.

 

proc sort data=have;

   by id job_cd;

run;

proc summary data=have;

   var year;

   by id job_cd;

   output out=years (drop=_type_ _freq_) min(year) = first_year max(year) = last_year;

run;

 

data want;

merge have years;

by id job_cd;

run;

Solution
‎04-05-2018 04:37 AM
Super User
Posts: 10,238

Re: Trying to preprocess the sas dataset

Posted in reply to Astounding

Be cautious. Just grouping by id and job_cd will not solve the fact that id 1 has two separate periods with job_cd = 4.

I propose some trickery:

data
  int1 (keep=id job_cd period year)
  int2 (keep=id period first_year last_year)
;
set work;
by id job_cd notsorted;
retain period_count first_year;
if first.id then period = 0;
if first.job_cd
then do;
  period + 1;
  first_year = year;
end;
if last.job_cd
then do;
  last_year = year;
  output int2;
end;
output int1;
run;

data want;
merge
  int1
  int2
;
by id period;
drop period;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Trying to preprocess the sas dataset

Posted in reply to KurtBremser
Thanks for saving my day. you are the best.
New Contributor
Posts: 3

Re: Trying to preprocess the sas dataset

Posted in reply to Astounding
while it's useful. it is not quite i was looking for. But thanks anyway.
Super User
Posts: 10,238

Re: Trying to preprocess the sas dataset

You can use @Astounding's method by first creating a period variable like I did and then use that in the proc summary and the merge instead of job_cd.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 146 views
  • 1 like
  • 3 in conversation