topic Re: proc expand - a problem with converting data from yearly to quarterly in SAS Procedures

proc expand - a problem with converting data from yearly to quarterly

chris2377 — Thu, 15 Jan 2015 14:12:16 GMT

Dear all;

I have a dataset with yearly observations that I want to transform to have quarterly frequency.

data have;

input year country $:1. value;

datalines;

2000 A 1

2001 A 0

2000 B 1

;

run;

data have;

set have;

format year year4.;

run;

data want;

input period :$6. country :$1. value;

datalines;

2000Q1 A 1

2000Q2 A 1

2000Q3 A 1

2000Q4 A 1

2001Q1 A 0

2001Q2 A 0

2001Q3 A 0

2001Q4 A 0

2000Q1 B 1

2000Q2 B 1

2000Q3 B 1

2000Q4 B 1

2001Q1 B 1

2001Q2 B 1

2001Q3 B 1

2001Q4 B 1

;

run;

I've tried to use proc expand in the following way (following SAS documentation to the procedure):

proc expand data=have out=want from=year to=qtr;

convert value;

id year;

by country;

run;

but got the following error message:

ERROR: Duplicate time interval found at observation number 2 in the data set WORK.HAVE,

according to the FROM=YEAR option and the ID variable values. The current ID is year=2001

and the previous is year=2000, which are within the same YEAR interval.

Check that FROM=YEAR is correct for this data set, and that the ID variable year contains

SAS date or datetime values that correctly identify the observations.

NOTE: Execution is aborted because of invalid ID values or invalid FROM= option.

NOTE: The above message was for the following BY group:

country=A

ERROR: Observation with duplicate ID value found. The value of the ID variable, year=2000, at

observation number 4 in data set WORK.HAVE is the same as the previous observation.

What am I doing wrong?

Best,

Chris

Re: proc expand - a problem with converting data from yearly to quarterly

user24feb — Thu, 15 Jan 2015 14:16:19 GMT

You have a typo. Start year and ending year of B is both 2000. There is nothing to interpolate for proc expand.

Re: proc expand - a problem with converting data from yearly to quarterly

chris2377 — Thu, 15 Jan 2015 14:28:28 GMT

Thanks for noting, but it's not the cause. I've corrected the typo and still get the error message

data have;

input year country $:1. value;

datalines;

2000 A 1

2001 A 0

2000 B 1

2001 B 1

;

run;

proc expand data=have out=want from=year to=qtr;

convert value;

id year;

by country;

run;

ERROR: Duplicate time interval found at observation number 2 in the data set WORK.HAVE,

according to the FROM=YEAR option and the ID variable values. The current ID is year=2001

and the previous is year=2000, which are within the same YEAR interval.

Check that FROM=YEAR is correct for this data set, and that the ID variable year contains

SAS date or datetime values that correctly identify the observations.

NOTE: Execution is aborted because of invalid ID values or invalid FROM= option.

NOTE: The above message was for the following BY group:

country=A

ERROR: Duplicate time interval found at observation number 4 in the data set WORK.HAVE,

according to the FROM=YEAR option and the ID variable values. The current ID is year=2001

and the previous is year=2000, which are within the same YEAR interval.

Check that FROM=YEAR is correct for this data set, and that the ID variable year contains

SAS date or datetime values that correctly identify the observations.

NOTE: Execution is aborted because of invalid ID values or invalid FROM= option.

NOTE: The above message was for the following BY group:

country=B

NOTE: There were 4 observations read from the data set WORK.HAVE.

NOTE: The data set WORK.WANT has 0 observations and 3 variables.

NOTE: PROCEDURE EXPAND used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Re: proc expand - a problem with converting data from yearly to quarterly

Ksharp — Thu, 15 Jan 2015 14:31:02 GMT

Here is a data step version solution.

data have;
  input year country $:1. value;
  datalines;
  2000 A 1
  2001 A 0
  2000 B 1
  2001 B 1
  ;
run;
data want;
 set have;
 do i=1 to 4 ;
  date=yyq(year,i);
  output;
 end;
 format date yyq.;
 drop i year;
run;

Xia Keshan

Re: proc expand - a problem with converting data from yearly to quarterly

chris2377 — Thu, 15 Jan 2015 14:34:51 GMT

Thanks, it gives me what I want.

Best

Re: proc expand - a problem with converting data from yearly to quarterly

user24feb — Thu, 15 Jan 2015 14:39:28 GMT

You're right. Proc expand requires a date. That caused the error. This would work without errors, but I am afraid there is no interpolation method like you described above.

data have;
input year country $:1. value;
YearDate=MDY(1,1,Year); Format YearDate Date9.;
datalines;
2000 A 1
2001 A 0
2000 B 1
2001 B 1
;
run;

Proc Expand Data=Have Out=Want From=Year To=Qtr;
Convert value=value_new / Observed=Ending;
ID YearDate;
By country;
Run;

Re: proc expand - a problem with converting data from yearly to quarterly

chris2377 — Thu, 15 Jan 2015 14:56:07 GMT

Thanks for help. I didn't notice the problem with date format. Dates in SAS are still tricky for me from time to time.

Best