Quarterly to Annual data

[newSAS2017]

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!

[PeterClemmensen]

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

And can you provide some sample data? 

[newSAS2017]

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

[Kurt_Bremser]

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

[Kurt_Bremser]

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;

[PeterClemmensen]

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

[newSAS2017]

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?

[newSAS2017]

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

[Kurt_Bremser]

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.

[newSAS2017]

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"

[Kurt_Bremser]

Do

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

if last.variable then output;