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!
Are you looking for an IML solution to this problem or a data step solution? 🙂
And can you provide some sample 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
1998 | variable 1 | 11 |
1998 | variable 2 | 5 |
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
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;
See @Kurt_Bremser's solution. Though, I fail to see why you want to use a macro or the purpose of the IML step? 🙂
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?
I don't think I can really use cards...data goes into the hundreds of rows
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.
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:
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"
Do
by year variable; * no quart!;
....
if first.variable then do;
.....
if last.variable then output;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.