topic Re: Trying to preprocess the sas dataset in SAS Programming

Trying to preprocess the sas dataset

nor5teo — Thu, 05 Apr 2018 01:25:19 GMT

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.

Re: Trying to preprocess the sas dataset

Astounding — Thu, 05 Apr 2018 03:35:53 GMT

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;

Re: Trying to preprocess the sas dataset

Kurt_Bremser — Thu, 05 Apr 2018 07:06:34 GMT

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;

Re: Trying to preprocess the sas dataset

nor5teo — Thu, 05 Apr 2018 08:38:02 GMT

while it's useful. it is not quite i was looking for. But thanks anyway.

Re: Trying to preprocess the sas dataset

nor5teo — Thu, 05 Apr 2018 08:39:54 GMT

Thanks for saving my day. you are the best.

Re: Trying to preprocess the sas dataset

Kurt_Bremser — Thu, 05 Apr 2018 08:41:57 GMT

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.