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

[chris2377]

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

  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

[user24feb]

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

[chris2377]

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

[user24feb]

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;

[chris2377]

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

[Ksharp · Accepted solution]

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

[chris2377]

Thanks, it gives me what I want.

Best