BookmarkSubscribeRSS Feed
newSAS2017
Calcite | Level 5

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!

 

 

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

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

 

And can you provide some sample data? 

newSAS2017
Calcite | Level 5

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
Kurt_Bremser
Super User
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
Super User

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
Tourmaline | Level 20

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
Calcite | Level 5

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
Calcite | Level 5

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

Kurt_Bremser
Super User

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
Calcite | Level 5

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"

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

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.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 10 replies
  • 1641 views
  • 1 like
  • 3 in conversation