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
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
You have a typo. Start year and ending year of B is both 2000. There is nothing to interpolate for proc expand.
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
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;
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
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
Thanks, it gives me what I want.
Best
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.