Statistical programming, matrix languages, and more

Quarterly to Annual data

Reply
Occasional Contributor
Posts: 8

Quarterly to Annual data

I have been running someone elses code previously which brought in an annual data set [111 rows, 1 column]

The input data file has now been changed to have quarterly data [444 rows, 1 column]. Not sure how I can write a bit of code that sums every 4 rows to create the original [111, 1] format?

 

data original_data_&year;

set new_data;

where year=&year;

keep t_1 ;

run;

 

 Thanks!

 

 

PROC Star
Posts: 653

Re: Quarterly to Annual data

[ Edited ]

Are you looking for an IML solution to this problem or a data step solution? Smiley Happy

 

And can you provide some sample data? 

Occasional Contributor
Posts: 8

Re: Quarterly to Annual data

Hi,

 

It is written as a macro within the code

 

%macro new_spend;

data original_data_&year;

set new_data;

where year = &year; 

keep t_1;

run;

 

proc iml;

 

 

with sample data:

1998  variable 1 4 
1998  variable 1 2 
1998  variable 1 3 
1998  variable 1 2 
1998  variable 2 0 
1998  variable 2 2 
1998  variable 2 3 
1998  variable 2 0 

 when I really want

1998variable 111
1998variable 25
Super User
Posts: 7,444

Re: Quarterly to Annual data

data have;
infile cards dlm=',';
length year 3 text $12 value 8;
input year text value;
cards;
1998,variable 1,4
1998,variable 1,2
1998,variable 1,3
1998,variable 1,2
1998,variable 2,0
1998,variable 2,2
1998,variable 2,3
1998,variable 2,0
;
run;

data want;
set have;
by year text;
retain sum;
if first.text then sum = 0;
sum + value;
if last.text then output;
drop value;
run;

proc print data=want noobs;
run;

Note how example data should be posted for convenience.

The result:

year       text       sum

1998    variable 1     11
1998    variable 2      5
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,444

Re: Quarterly to Annual data

You can also use proc summary, unless you need some more complicated logic:

proc summary data=have;
by year text;
var value;
output out=want (drop=_type_ _freq_) sum=;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 653

Re: Quarterly to Annual data

See @KurtBremser's solution. Though, I fail to see why you want to use a macro or the purpose of the IML step? Smiley Happy

Occasional Contributor
Posts: 8

Re: Quarterly to Annual data

Thanks

 

Sorry I'm REALLY new to SAS! - Think we are using a macro so that it can be called later in the long process flow.

 

In the above solution are you taking year, text and value as the column titles in the data set?

Occasional Contributor
Posts: 8

Re: Quarterly to Annual data

I don't think I can really use cards...data goes into the hundreds of rows

Super User
Posts: 7,444

Re: Quarterly to Annual data

You don't need to post a complete dataset. Just enough to illustrate your issue. Like the 8 rows you provided. The data step method is just so we can simply copy/paste the step into our SAS, run it and work with the data.

After all, we all have a job that earns us money and takes precedence.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: Quarterly to Annual data

I have tried to apply this to this data:

yearquartVariabletime1time2
19981110.6
19982120.9
19983121
19984110.6
19981200
19982200
19983200
19984200
19981300
19982300
19983300
19984300
19981400
1998240.10
1998340.10
1998440

0

 

In the hope of achieving :

yearVariablevalue1value2
1998163.1
1998200
1998300
199840.20

 

data original (keep = year quart variable value );

set newdata;

by year variable quart ;

if first.quart then do;

value = 0;

value2 = 0;

 

end;

 

value1 + time1;

value2 + time2;

 

if last.quart then output;

run;

 

 

but when I run the code it is coming out as:

 

newdata
1998111
1998212
1998312

 

with the error

"ERROR: BY variables are not properly sorted on data set"

 

Super User
Posts: 7,444

Re: Quarterly to Annual data

Do

by year variable; * no quart!;
....
if first.variable then do;
.....

if last.variable then output;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 10 replies
  • 442 views
  • 1 like
  • 3 in conversation