topic Re: Quarterly to Annual data in SAS/IML Software and Matrix Computations

Quarterly to Annual data

newSAS2017 — Thu, 12 Jan 2017 14:32:53 GMT

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!

Re: Quarterly to Annual data

PeterClemmensen — Thu, 12 Jan 2017 14:40:46 GMT

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

And can you provide some sample data?

Re: Quarterly to Annual data

newSAS2017 — Thu, 12 Jan 2017 14:47:22 GMT

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

1998	variable 1	11
1998	variable 2	5

Re: Quarterly to Annual data

Kurt_Bremser — Thu, 12 Jan 2017 14:57:39 GMT

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

Re: Quarterly to Annual data

Kurt_Bremser — Thu, 12 Jan 2017 15:00:34 GMT

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;

Re: Quarterly to Annual data

PeterClemmensen — Thu, 12 Jan 2017 15:03:06 GMT

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

Re: Quarterly to Annual data

newSAS2017 — Thu, 12 Jan 2017 15:21:28 GMT

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?

Re: Quarterly to Annual data

newSAS2017 — Thu, 12 Jan 2017 15:23:52 GMT

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

Re: Quarterly to Annual data

Kurt_Bremser — Thu, 12 Jan 2017 15:45:05 GMT

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.

Re: Quarterly to Annual data

newSAS2017 — Fri, 13 Jan 2017 11:01:23 GMT

I have tried to apply this to this data:

year	quart	Variable	time1	time2
1998	1	1	1	0.6
1998	2	1	2	0.9
1998	3	1	2	1
1998	4	1	1	0.6
1998	1	2	0	0
1998	2	2	0	0
1998	3	2	0	0
1998	4	2	0	0
1998	1	3	0	0
1998	2	3	0	0
1998	3	3	0	0
1998	4	3	0	0
1998	1	4	0	0
1998	2	4	0.1	0
1998	3	4	0.1	0
1998	4	4	0	0

In the hope of achieving :

year	Variable	value1	value2
1998	1	6	3.1
1998	2	0	0
1998	3	0	0
1998	4	0.2	0

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

1998	1	1	1
1998	2	1	2
1998	3	1	2

with the error

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

Re: Quarterly to Annual data

Kurt_Bremser — Fri, 13 Jan 2017 11:38:27 GMT

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

if last.variable then output;